Using Set-Analysis to obtain Previous Month In a Pivot-Table with Time Dimension

[#1]

Hi,

Please advise if doable:

- Input : Two tables – Calendar, Facts

- Objective : Create a Pivot-Table chart with a Time Dimension (Year/Quarter/Month) and two expressions:

1. Sum(Amount)
2. Amount of LAST month above the current row (regardless if the Dimension is Year, Quarter or month)

-  I could NOT figure out how to set the second expression utilizing Set-Analysis. Is it doable ?

- Please see attached QVW.

Thanks!

Shaul

[preminqlik]

once try this

sum( aggr(above(sum({1}Amount)),%REPORT_MONTH_ID))

[Gysbert_Wassenaar]

You can't use set analysis. The set is calculated per chart, not per dimension value. Try this instead:

FirstSortedValue(aggr(sum(Amount),m.RowId),-m.RowId,2)

[#4]

Hi Prem Kumar,

Thanks for your reply. It seems to be working ONLY when the Time dimension is Month  (due to the use of the Above() function.)  I need to be able to get the previous month above the current row regardless if it is scope(Year, Quarter, or Month).

Any idea?

Thanks,

Shaul

[#5]

Hi Gysbert,

Thanks for your reply. This doesn't seem to be working.. I need to get the monthly amount right above the row I'm in (regardless if the time-dimension being used is Year, Quarter, or month)

Any idea?

Thanks,

Shaul

[Gysbert_Wassenaar]

Works for me. You said you wanted "Amount of LAST month above the current row (regardless if the Dimension is Year, Quarter or month)".

That's exactly what the expression does.If you want sum(Amount) for the previous Year if the dimension is Year then you need another expression. The easiest is to use above(sum(Amount))