I am working on a worksheet that contains category sales information over 3 years as well as year over year calculations.
I would like to sort categories on a particular date, based on the highest % change of sales over last year.
I know that I can create a calculated field, convert it from a measure to a dimension and sort my categories based on that field. However, I can't figure out the right logic.
It should be something like this:
If ([SalesDate]) = '2/11/11'
Then (ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1)) / ABS(LOOKUP(ZN(SUM([Sales])), -1)) ...... here I would like to place an expression that tells the system to sort based on % change of sales over last year