Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
tolberta14
Partner - Contributor III
Partner - Contributor III

Set Analysis Over Dimension

I'd like to use set analysis to do point in time reporting across a Fiscal Year. So the current Month, Current month-12....etc.

Sum(

{$<MonthID = {$(=max(MonthID))}

>}

LineSaleAmt)


However the Max(MonthId) part of the set analysis evaluates once, and only once.  This makes for a useful and dynamic KPI widget, but not across a dimension.


I'd like to replicate the second measure in this chart, which uses a current year flag, but using the monthID instead.

Set Analysis.PNG

Is there a way to get the Max(MonthID) to be evaluated accross a dimension?

1 Solution

Accepted Solutions
ericasense
Contributor III
Contributor III

Hi Andrew,

Some other alternatives if you didn't want to mess with the script you could either use an if() statement to compare the latest monthID or use FirstSortedValue combined with aggr to get what you need.

Something like:

=Sum(if(MonthID=Max(MonthID),LineSaleAmt))

Or

=FirstSortedValue(aggr(sum(LineSalesAmt),MonthID),-MonthID

Both solutions would not be very efficient for large amounts of data though.

Erica

View solution in original post

2 Replies
sunny_talwar

Set analysis, by design is only evaluated once per chart. This is the very reason which makes it more efficient then using if condition which evaluated on row by row basis. From what I understand, you have two options

1) Use Above() or Below() function

2) Use The As-Of Table‌. This is a slightly better approach, but requires some intervention in script. But if you are okay with adding a new table in your script... this would be the way to go

ericasense
Contributor III
Contributor III

Hi Andrew,

Some other alternatives if you didn't want to mess with the script you could either use an if() statement to compare the latest monthID or use FirstSortedValue combined with aggr to get what you need.

Something like:

=Sum(if(MonthID=Max(MonthID),LineSaleAmt))

Or

=FirstSortedValue(aggr(sum(LineSalesAmt),MonthID),-MonthID

Both solutions would not be very efficient for large amounts of data though.

Erica