9 Replies Latest reply: Jan 6, 2015 6:29 AM by Aaron Morgan

# 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!

• ###### Re: Set Analysis Not Returning Expected Results?

try with:

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

• ###### Re: Set Analysis Not Returning Expected Results?

That simply returns 0 for every account unfortunately!

• ###### Re: Set Analysis Not Returning Expected Results?

And now?

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

• ###### Re: Set Analysis Not Returning Expected Results?

I'm sorry is the same response @anbu_cheliyan

• ###### Re: Set Analysis Not Returning Expected Results?

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

• ###### Re: Set Analysis Not Returning Expected Results?

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

• ###### Re: Set Analysis Not Returning Expected Results?

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!

• ###### Re: Set Analysis Not Returning Expected Results?

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.

• ###### Re: Set Analysis Not Returning Expected Results?

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.