Making BELOW() insensitive to user selections

imark — Wed, 16 Oct 2019 11:27:46 GMT

The title tells only part of the story. Apologies, but I really had no idea how to title this issue in a few words

I was asked to provide a table that shows the daily value of sales and the value of sales on the same day one year ago.

The way I approached this was by creating a straight table having one dimension (date) and two metrics:

Sales = SUM(Sales)

Sales last year = BELOW(SUM(Sales), 365)

This approach delivers as expected until the point the user starts fiddling with the Date Listbox, which is a necessary part of the deliverable.

Let's assume that the first three rows in the table look as follows:

Date	Sales	Sales last year
2019.10.14	158 $	169 $
2019.10.13	174 $	165 $
2019.10.12	161 $	170 $

With the current setup, if the end user selects '2019.10.13' in the Listbox what will happen is that 'Sales last year' will turn to 0 (because the relevant row, that is supposed to be 365 places below simply does not "exist")

Date	Sales	Sales last year
2019.10.13	174 $	0 $

I tried different approaches with set analysis, e.g. by changing the second metric to:

Sales last year = BELOW(SUM{1} Sales), 365) which allows me to retain the 165 $ value but it also forces all dates to remain visible

Date	Sales	Sales Last Year
2019.10.14	0	169 $
2019.10.13	174 $	165 $
2019.10.12	0	170 $

None of the above outlined outcome matches the user experience that I want to deliver to my stakeholders. If a user selects only one date via listbox then the table should be reduced to one row, but both the Sales and Sales Last Year metrics should be populated with the correct data.

One solution that I can think of is to create the final table already at script execution time, but if there is a way to do it in the front-end, I would prefer it by far.

Thank you.

Re: Making BELOW() insensitive to user selections

sunny_talwar — Wed, 16 Oct 2019 11:31:51 GMT

Give this a shot

=Below(Sum{1} Sales), 365) * Avg(1)

=Below(Sum{<Date>} Sales), 365) * Avg(1)

Re: Making BELOW() insensitive to user selections

imark — Wed, 16 Oct 2019 12:11:17 GMT

Thank you! Both solutions work like a charm! But I don't really understand why, in particular what the role of AVG(1) is.

Re: Making BELOW() insensitive to user selections

sunny_talwar — Wed, 16 Oct 2019 12:13:44 GMT

Avg(1) = 1 for your selections and 0 for out of selections.

topic Making BELOW() insensitive to user selections in QlikView

Making BELOW() insensitive to user selections

Re: Making BELOW() insensitive to user selections

Re: Making BELOW() insensitive to user selections

Re: Making BELOW() insensitive to user selections