topic Re: Limit KPI by time range in set analysis using variables in App Development

Limit KPI by time range in set analysis using variables

Kellerassel — Wed, 10 Nov 2021 13:40:22 GMT

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?

Re: Limit KPI by time range in set analysis using variables

rubenmarin — Wed, 10 Nov 2021 15:57:11 GMT

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])

Re: Limit KPI by time range in set analysis using variables

Kellerassel — Thu, 11 Nov 2021 09:08:49 GMT

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?

Re: Limit KPI by time range in set analysis using variables

rubenmarin — Thu, 11 Nov 2021 14:24:52 GMT

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?