Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to compare current and prior values

I have a case where I need a label "Decrease" or "Increase" after checking row by row in a pivot table. Below sample, 'Day 3' is my current Data and 'Day 2' is my Prior data for sample purposes. If TheCount in current day is > prior day at Bus level, I want a label "Increase". as illustrated below Apple will get an "Increase"

If TheCount in current day is < prior day at Bus level, I want a label "Decrease". as illustrated below Google will get an "Decrease"

The label can be the final expression in the pivot table. I tried working with above/below, aggr,rank,only but I am stuck. Would greatly appreciate any help.


**Day1 data is ignored, becoz the comparison sud happen with only 2 data points


Bus      TMonth      Day     TheCount

Apple      Sep                  1         10

Apple      Sep                  2         11

Apple      Sep                 3         12

Google     Sep               1         11

Google     Sep              2         13

Google     Sep             3         12

1 Solution

Accepted Solutions
sunny_talwar

May be this

If(TheCount > Above(TheCount), 'Increase', If(TheCount < Above(TheCount), 'Decrease'))

View solution in original post

3 Replies
Anonymous
Not applicable
Author

sunny_talwar

May be this

If(TheCount > Above(TheCount), 'Increase', If(TheCount < Above(TheCount), 'Decrease'))

Anonymous
Not applicable
Author

Hi Sunny Thanks again for answering. I was trying to approach with below but it was working only in straight table. was not as elegant as yours. Actually far from it. anyway I tried

Regards,

"if(FirstSortedValue(TheCount,-aggr(Only(Day),Bus, Day),1)

> FirstSortedValue(TheCount,-aggr(Only(Day),Bus, Day),2),

'Increase',

if(FirstSortedValue(TheCount,-aggr(Only(Day),Bus, Day),1)

= FirstSortedValue(TheCount,-aggr(Only(Day),Bus, Day),2), 'No Change',

'Decrease'))"