topic Re: Pivot tables in QlikView

Pivot tables

arpitkharkia — Wed, 17 Oct 2018 10:13:26 GMT

Hi folks,

This question is with respect to Qlikview. Please consider the below table.

Date	Open	Closed
17-10-2018	30	15
18-10-2018	22	54
19-10-2018	32	14
20-10-2018	11	15
Variance	-21	1

I have two dimensions DATE and Indicator (Open and Closed ) and one expression count. I need a variance row in the last row that is the difference between two recent dates, in this case 20th oct and 19th oct. How can i get that variance row with respect two the most recent dates and that too in the pivot table itself.

Thanks for the help!

Re: Pivot tables

sunny_talwar — Wed, 17 Oct 2018 10:31:26 GMT

May be like this

If(Dimensionality() = 0, Count({<Date = {"$(=Date(Max(Date)))"}>} Measure)-Count({<Date = {"$(=Date(Max(Date, 2)))"}>} Measure), Count(Measure))

and then make sure to enable subtotals from the presentation tab

Re: Pivot tables

arpitkharkia — Wed, 17 Oct 2018 10:51:12 GMT

Hey Sunny,

Thanks for the quick reply, but im not still not getting the required output. Im attaching a sample file.

Re: Pivot tables

sunny_talwar — Wed, 17 Oct 2018 13:40:11 GMT

My bad with the syntax (fixed now)... try this for your sample

=If(Dimensionality() = 0,

Count({<Date = {"$(=Date(Max(Date)))"}>} Indicator)-Count({<Date = {"$(=Date(Max(Date, 2)))"}>} Indicator),

Count(Indicator))

Re: Pivot tables

arpitkharkia — Tue, 23 Oct 2018 08:40:37 GMT

This working perfectly. I was not sure we could have user Dimensionality() in such a way.

Thanks again!