Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
morganaaron
Specialist
Specialist

Set Analysis Not Returning Expected Results?

Hi all,

I've got an expression that works out the last time an account traded:

Max({<PeriodNumber, Year, Sales={'>0'}>}Period)"}

This works fine, and regardless of my selections returns the correct period for each account that it last traded in in a straight table (with Account as the dimension).

What I'm trying to do is to return the sales total for each account for their last period traded and add this an expression to my table, but it seems to be returning a sum of periods for the value (not just the period I want for each account). The expression I'm using is:

Sum({<Year, PeriodNumber, Period = {"=Max({<PeriodNumber, Year, Sales = {'>0'} >} Period)"} >} Sales)

I'm sure it's something simple, but any suggestions as to why it's not just returning the value for the max period?

Thanks!

9 Replies
ecolomer
Master II
Master II

try with:

Sum({<Year, PeriodNumber, Period = {"Max({<PeriodNumber, Year, Sales = {'>0'} >} Period)"} >} Sales)

morganaaron
Specialist
Specialist
Author

That simply returns 0 for every account unfortunately!

anbu1984
Master III
Master III

Sum({<Year=, PeriodNumber=, Period = {"=$(=Max({<Sales={'>0'}>}Period))"} >} Sales)

ecolomer
Master II
Master II

And now?

Sum({<Year =, PeriodNumber =, Period = {"=$ (=Max({<Sales = {'>0'} >} Period))"} >} Sales)

morganaaron
Specialist
Specialist
Author

This only seems to return values where the last trading period is equal to the maximum period and gives a 0 for anything else, halfway there though!

ecolomer
Master II
Master II

I'm sorry is the same response @anbu_cheliyan

morganaaron
Specialist
Specialist
Author

Same as Anbu's answer - only returns the value for the accounts where the Period last traded is also the maximum period.

tresesco
MVP
MVP

The basic reason why it would not work is : Set analysis doesn't get evaluated row-wise. You need max date against every accounts which is in dimension. Direct set analysis would not help here. I guess you can try it with FirstSortedValue(). Try to share a sample qvw to get help in more details. 

morganaaron
Specialist
Specialist
Author

Ah, so I can calculate it row wise but not use it inside of a set row wise. Makes sense. I'll play around with FSV and see where I get to!

Thanks.