3 Replies Latest reply on Dec 3, 2018 10:20 AM by Paul Wachtler

# Month on Month Change for Pivot Values

Hi,

I am having trouble showing RAG status and trend for Pivot Values by using Date filter. The following formula SUM([Pivot Field Values])-LOOKUP(SUM([Pivot Field Values]),-1) is returning differences between the rows when I was looking for differences within the date range.

Many thanks.

• ###### 1. Re: Month on Month Change for Pivot Values

Hi Agata,

Window calcs (like lookup) only work with fields that are in your viz - on the row or column shelf or on most marks cards (not tooltips).  You only have your date on the filter pane so it cant be used by the lookup calculation.

Can you explain a litlle more about what you'd like to accomplish?  For the 4 month period in your viz do you want to see the average MoM change for each pivot field value?  Or do you want individual MoM values for each month (after the first month) in that date period for each pivot field value?

Best,

Paul

• ###### 2. Re: Month on Month Change for Pivot Values

Hi Paul,

Thank you. I'd like to show the trend whether the numbers are going up or down for each category in the selected time frame. It could be shown as the end date value less average for the period or end date value less start date value (whichever is easier).

Many thanks

Agata

• ###### 3. Re: Month on Month Change for Pivot Values

Thanks for clearing that up Agata.  Unfortunately the way that you have this structured makes it hard to see which values fall between that date range.  You can aggregate the values (sum, avg, max, etc) but finding the last value and subtracting the first is difficult without actually putting the date onto your viz.

The only solution I can think of would involve adding month to your viz, using a table calculation to figure out the change from the first value to the last, and then hiding the month values.  The only downside with this is that your date filter wouldn't work anymore, so I don't think that's a viable option for you.

I'm hoping someone else here on the forums may have another idea.

Best,

Paul