Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be this
If(TheCount > Above(TheCount), 'Increase', If(TheCount < Above(TheCount), 'Decrease'))
May be this
If(TheCount > Above(TheCount), 'Increase', If(TheCount < Above(TheCount), 'Decrease'))
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'))"