topic Re: Using Set-Analysis to obtain Previous Month In a Pivot-Table with Time Dimension in QlikView

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

Thu, 01 May 2014 16:21:04 GMT

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:

Sum(Amount)
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

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

preminqlik — Thu, 01 May 2014 17:59:33 GMT

once try this

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

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

Gysbert_Wassenaar — Thu, 01 May 2014 18:08:20 GMT

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)

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

Thu, 01 May 2014 20:16:20 GMT

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

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

Thu, 01 May 2014 20:21:50 GMT

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

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

Gysbert_Wassenaar — Fri, 02 May 2014 05:49:03 GMT

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))