Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
imark
Contributor III
Contributor III

Making BELOW() insensitive to user selections

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:

DateSalesSales last year 
2019.10.14158 $169 $
2019.10.13174 $165 $
2019.10.12161 $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")

DateSalesSales last year 
2019.10.13174 $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

DateSalesSales Last Year
2019.10.140169 $
2019.10.13174 $165 $
2019.10.120170 $

 

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.

1 Solution

Accepted Solutions
sunny_talwar

Give this a shot

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

or

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

 

View solution in original post

3 Replies
sunny_talwar

Give this a shot

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

or

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

 

imark
Contributor III
Contributor III
Author

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

sunny_talwar

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