Skip to main content
Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
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'))"