topic How to filter the display but not the calculations? in App Development

How to filter the display but not the calculations?

QlikArno — Mon, 13 Jan 2020 13:38:56 GMT

Hi Qlik Community,

For my dashboard I would like to display the five most recent rows of a larger data set. However, the content of other fields in this table is depending on previous rows that changes when I filter on date. The rows that are filtered are hereby not taken into account in the calcutations anymore. For example, when I filter the five most recent rows in the table:

Date	Measure
1 January	1
2 January	2
3 January	3
4 January	4
5 January	5
6 January	6
7 January	7
8 January	8
9 January	9
10 January	10
11 January	11
12 January	12
13 January	13

I get:

Date	Measure
9 January	1
10 January	2
11 January	3
12 January	4
13 January	5

whereas the desired outcome is:

Date	Measure
9 January	9
10 January	10
11 January	11
12 January	12
13 January	13

What do I need to do to get the third table?

Thank you in advance for your suggestions,

Arno van Akkeren

Re: How to filter the display but not the calculations?

QlikArno — Thu, 16 Jan 2020 07:28:22 GMT

Any thoughts about this issue? Your suggestions will be appreciated!

Re: How to filter the display but not the calculations?

anushree1 — Thu, 16 Jan 2020 09:05:54 GMT

how is the measure column calculated for the given data?

Re: How to filter the display but not the calculations?

Kushal_Chawda — Thu, 16 Jan 2020 09:36:10 GMT

do you want latest records from script itself? what is the unique key in your data?

Re: How to filter the display but not the calculations?

QlikArno — Thu, 16 Jan 2020 10:13:39 GMT

Thank you for your reply. I have this issue with several columns. For example:

If( Sum(Measure) > Above(Sum(Measure),1)
AND Above(Sum(Measure),1) > Above(Sum(Measure),2)
AND Above(Sum(Measure),2) > Above(Sum(Measure),3)
AND Above(Sum(Measure),3) > Above(Sum(Measure),4)
AND Above(Sum(Measure),4) > Above(Sum(Measure),5)
AND Above(Sum(Measure),5) > Above(Sum(Measure),6)
, '1'
, '0')

In this example a data set of at least seven days is required. I want to display the last five days. However, I want the days in prior to the five day period to be part of the calculation. Using the logic above in the column Calculation:

Date	Measure	Calculation
1 January	1	0
2 January	2	0
3 January	3	0
4 January	4	0
5 January	5	0
6 January	6	0
7 January	7	1
8 January	8	1
9 January	9	1
10 January	10	1
11 January	11	1
12 January	12	1
13 January	13	1

The desired result is:

Date	Calculation
9 January	1
10 January	1
11 January	1
12 January	1
13 January	1

The result I get:

Date	Calculation
9 January	0
10 January	0
11 January	0
12 January	0
13 January	0

Re: How to filter the display but not the calculations?

QlikArno — Thu, 16 Jan 2020 10:36:36 GMT

Thank you for your reply.

I'm not sure what you mean with 'from script itself'. I want to display the last five rows in the table that I get when I run the code.

The unique key consists of the fields MeasureName and Date. I didn't mention the column MeasureName in the example because I didn't think it was relevant.

Re: How to filter the display but not the calculations?

Kushal_Chawda — Thu, 16 Jan 2020 11:00:17 GMT

Create table with Dimension Date below expression

= if(ceil(((Count(total Date)-5)+1)/RowNo(total))=1,1,0)

Note:

If you want to show last 7 change the number highlighted in red to 7. Also make sure that the Date is sorted in ascending order. Go to chart properties->Add-on-> Uncheck 'Include suppress zero' value