Limit KPI by time range in set analysis using variables

[Kellerassel]

I want to be able to limit a KPI, let's say turnover, to a specific time range which I chose by setting two variables: the lower bound and the upper bound.

I have created two tables, which I derive from the calendar-table.

month_start:
LOAD MonthYear as MonthStart
RESIDENT Calendar;

month_end:
LOAD MonthYear as MonthEnd
RESIDENT Calendar;

I create two filters, one for MonthStart and one for MonthEnd.

I set two variables. I know that GetFieldSelections()returns a string so I convert it back to a date.

=date#(GetFieldSelections(MonthEnd), 'MM YYYY')

=date#(GetFieldSelections(MonthStart), 'MM YYYY')

Finally I use this expression to calculate the KPI:

SUM({$< MonthYear = {"<$(=vEndMonth)>=$(=vStartMonth)"}>} [turnover])

But it doesn't work. The measure returns 0,00€ (the data is definitly there though).

What am I doing wrong? What am I missing?

[rubenmarin]

Hi, there are some things to comment:

• - GetFieldSelections can return many different values if the user selects more than one month, that can break the logic.
• Confirm that the original MonthYear field is a number, you can check adding a filter or this field as dimension in a table, if it's left aligned is a text, if it's right-aligned is a number (internally a date is a number)
• - I think that Qlik is using vEndMonth and vStartMonth as numbers, and the space is breaking that number
• - If the space is not breaking the number the logic would work either as may-2021 wil be lower than oct-2019 because 052021 is a number lower than 102019.
• Format issues are very common when filtering by dates

To solve this points you can use

• avoid most of this comments I usually have a date in a number format to avoid format issues, from the MonthYear field you can create the date as number as:

Num(MakeDate(Year(MonthYear),Month(MonthYear))) as NumDate

Or if you want consecutive numbers

=(Year(MonthYear)*12)+Month(MonthYear) as NumYearMonth

• Use Max and Min to retrieve the lower and higher month

vStartMonth= Min(NumDate);  Min(NumYearMonth)

So the final expression can be

SUM({$< NumYearMonth= {"<$(=vEndMonth)>=$(=vStartMonth)"}>} [turnover])

[Kellerassel]

Thanks for your reply.

Let me first address your comments where necessary:

1. I have the "Always  one selected value"- field setting activated. So the filters for `MonthYear` always have one, and only one, value selected.

2. The MonthYear-field is created as `monthname(%date) as MonthYear` which allegedly returns a dual representation with a numeric value. It has tags `$numeric $integer $timestamp $date`

I'm not sure how to go about following your suggestions.

1. I can create a calendar-field 

Num(MakeDate(Year(MonthYear),Month(MonthYear))) as NumDate

2. But the user choses the Start-Month and End-Month in two separate filter panes. For these I need a readable date format so people know what they are selecting. A number doesn't work here.

3. So vStartMonth and vEndMonth should be numeric, but they need to be selected via a date formatted field. Therefore I changed the variable definition to:

= Num#(date#(GetFieldSelections(MonthStart), 'MM YYYY'))

So whatever the format in the filter pane, the variable definitely contains a number. But it still doesn't work 😞

As a note: I can't apply `date`, `num` or `monthname` around `date#(GetFieldSelections(MonthStart), 'MM YYYY'))`. Wonder why?

[rubenmarin]

Hi, the NumDate field would be an additional field in the Calendar table, then an expression like this should work:

SUM({$< NumDate= {"<$(=Num(Date#('$(vEndMonth)','MM YYYY')))>=$(=Num(Date#('$(vStartMonth)','MM YYYY')))"}>} [turnover])

But your last point can affect here, you should be able to use date or num around Date#, can you upload a dumb sample to check?