Search code examples
expressionspotfiretibco

Spotfire Data Limiting using Dropdowns

I have a spotfire dashboard with two dropdown lists, one for Day and one for Month, that I want to use to limit the data of visualizations.

enter image description here

I need to be able to use both filters at the same time. I currently have a case statement written out to do this however case statements by default stop evaluating expressions when they reach a true statement.
For example below, I have the statements for the MonthSelector on top, it does not continue to evaluate past that to the DaySelector. 

case

when "${MonthSelector}"="all" then [Month] <> ''

when "${MonthSelector}"<>"all" then [Month] = "${MonthSelector}"

when "${DaySelector}"="all" then [Week_Day] <> ''

when "${DaySelector}"="WeekDay" then [Week_Day] <> 'Saturday' and [Week_Day] <> 'Sunday'

when "${DaySelector}"="WeekEnd" then [Week_Day] = 'Saturday' OR [Week_Day] = 'Sunday'

when "${DaySelector}"<>"WeekEnd" AND "${DaySelector}"<>"WeekDay" AND "${DaySelector}"<>"all" then [Week_Day] = "${DaySelector}"
else false end case

I need assistance in either somehow getting spotfire to continue evaluating the case statement past the first true, or another way of writing a data limiting expression that will limit data based on both dropdowns.

I am at a loss, any help will be appreciated.

Solution

  • Can you try nesting the cases? Maybe like this untested expression:

    case
    when "${MonthSelector}"="all" then case
        when "${DaySelector}"="all" then [Week_Day] <> '' and [Month] <> ''
        when "${DaySelector}"="WeekDay" then [Week_Day] <> 'Saturday' and [Week_Day] <> 'Sunday' and [Month] <> ''
        when "${DaySelector}"="WeekEnd" then [Week_Day] = 'Saturday' OR [Week_Day] = 'Sunday' and [Month] <> ''
        when "${DaySelector}"<>"WeekEnd" AND "${DaySelector}"<>"WeekDay" AND "${DaySelector}"<>"all" then [Week_Day] = "${DaySelector}" and [Month] <> ''
        else false
        end
    when "${MonthSelector}"<>"all" then case
        when "${DaySelector}"="all" then [Week_Day] <> '' and [Month] = "${MonthSelector}"
        when "${DaySelector}"="WeekDay" then [Week_Day] <> 'Saturday' and [Week_Day] <> 'Sunday' and [Month] = "${MonthSelector}"
        when "${DaySelector}"="WeekEnd" then [Week_Day] = 'Saturday' OR [Week_Day] = 'Sunday' and [Month] = "${MonthSelector}"
        when "${DaySelector}"<>"WeekEnd" AND "${DaySelector}"<>"WeekDay" AND "${DaySelector}"<>"all" then [Week_Day] = "${DaySelector}" and [Month] = "${MonthSelector}"
        else false
        end
    else false
end