topic Re: How to compare current and prior values in QlikView

How to compare current and prior values

Anonymous — Wed, 20 Jun 2018 22:48:21 GMT

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

Re: How to compare current and prior values

Anonymous — Wed, 20 Jun 2018 23:39:40 GMT

stalwar1‌

Re: How to compare current and prior values

sunny_talwar — Thu, 21 Jun 2018 01:07:46 GMT

May be this

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

Re: How to compare current and prior values

Anonymous — Thu, 21 Jun 2018 02:28:40 GMT

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