Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
arpitkharkia
Creator III
Creator III

Pivot tables

Hi folks,

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

  

DateOpenClosed
17-10-20183015
18-10-20182254
19-10-20183214
20-10-20181115
Variance-211

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!

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

4 Replies
sunny_talwar

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

arpitkharkia
Creator III
Creator III
Author

Hey Sunny,

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

sunny_talwar

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

arpitkharkia
Creator III
Creator III
Author

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

Thanks again!