Previous Month in Pivot Expression

[Murlimes]

Hello,

whats the proper way to make an expression for the previous month in an pivot, without showing the previous month in the the Pivot dimension?

I can do it in a Chart with a VpreviousMonth Variable, but if i convert that Chart/expression into a pivot table, it doesnt work.

The Pivot will expand to both months and the previous month Expression will actually only be shown in the Dimension of the previous Month, but i want the expression to be in the actual selected month.

Thanks

[Murlimes]

just tried adding TOTAL and it seems to work, but it only shows now the previous  month Subtotal at every day.  (at least its the correct value)

I would still need to have the correct value for every single day and the correct value for the subtotal month.

I assume this has to be solved with an aggr function in some way like aggr(sum(total(<previousM>),Day))) but somehow i cant get it work, it will always show the subtotal of the previous month , in every single cell.

[MayilVahanan]

Hi @Murlimes 

Can you provide sample with ur scenario?

[Murlimes]

Hi,

I have charts where i use something like this

Sum({<[CAL.MonthStart] = {'$(vPriorMonthStart)'}, CAL.Year=, CAL.YearMonth=, CAL.YearWeek>}Value)

where PriorMontStart points to the previous month based on the user selection.

This works in every chart. If i put this epxression into a pivot table the pivot Table will expand to the previous month + current month. It will only show the previous month expression result in actual the previous month ( i understand this logic).

If i expand the epression with TOTAL

Sum(TOTAL{<[CAL.MonthStart] = {'$(vPriorMonthStart)'}, CAL.Year=, CAL.YearMonth=, CAL.YearWeek>}Value)

The pivot table will only show the Subtotal from the previous Month in every daily Cell of the currernt selected month. But i would like to see the amount for every single day + the correct subtotal

Thanks

Murlimes

[Chanty4u]

Im not  sure try this.

Expression

Sum(TOTAL{<[CAL.MonthStart]= {'$(=$(vPriorMonthStart))'}, CAL.Year=, CAL.YearMonth=, CAL.YearWeek>}Value)

[Murlimes]

This is will show the subtotal of the previous month in every cell (day)