Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

5 Replies
preminqlik
Specialist II
Specialist II

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)


talk is cheap, supply exceeds demand
Not applicable
Author

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

Not applicable
Author

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


talk is cheap, supply exceeds demand