Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Do More with Qlik - for Beginners and Beyond, Topic: Qlik Replicate on January 21, 2PM EST. REGISTER NOW
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
Specialist II
Specialist II

once try this

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

MVP & Luminary
MVP & Luminary

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

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

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

MVP & Luminary
MVP & Luminary

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