topic Re: Previous Month in Pivot Expression in QlikView

Previous Month in Pivot Expression

Murlimes — Fri, 20 Nov 2020 18:58:42 GMT

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

Re: Previous Month in Pivot Expression

Murlimes — Tue, 17 Nov 2020 08:50:12 GMT

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.

Re: Previous Month in Pivot Expression

MayilVahanan — Tue, 17 Nov 2020 09:22:17 GMT

Hi @Murlimes

Can you provide sample with ur scenario?

Re: Previous Month in Pivot Expression

Murlimes — Tue, 17 Nov 2020 14:04:00 GMT

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

Re: Previous Month in Pivot Expression

Chanty4u — Tue, 17 Nov 2020 16:05:59 GMT

Im not sure try this.

Expression

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

Re: Previous Month in Pivot Expression

Murlimes — Wed, 18 Nov 2020 10:38:59 GMT

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