HOW TO COMPARE CURRENT AND NEXT PERIOD IN A PIVOT TABLE

[Odla88]

Hi all,

I have an issue related a calculation of a formula between different years.

I have a pivot table that has as row dimension “outlet type”, as column dimension “year” and as measure sum(value).

My aim is to check if sum(value) related to dealers of each year + sum(value) related to opened delaers of next year - sum(value) related to closed delaers of next year = sum(value) related to dealers of next year. I want to show this check coloring the cells as sowed in the figure below.

I need to find a solution WITHOUT editing back-end script, and the solution must works even if I will add more years in the “year” dimension.

So this is my current situation

and this is what I need to obtain

I also attach a qvf. 

Thank you in advance to all!

[vinieme12 · Accepted solution]

Use before() 

https://help.qlik.com/en-US/sense/February2022/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/Inter...

Also use 3 measures one for each dealer type and one dimension i.e. Year 

Example syntax

=If(

(before(count({<Dealertype={'Dealers'}>}something))+count({<Dealertype={'OpenedDealers'}>}something))-count({<Dealertype={'ClosedDealers'}>}something))

)=count({<Dealertype={'Dealers'}>}something), green(),red() )