Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
try with:
Sum({<Year, PeriodNumber, Period = {"Max({<PeriodNumber, Year, Sales = {'>0'} >} Period)"} >} Sales)
That simply returns 0 for every account unfortunately!
Sum({<Year=, PeriodNumber=, Period = {"=$(=Max({<Sales={'>0'}>}Period))"} >} Sales)
And now?
Sum({<Year =, PeriodNumber =, Period = {"=$ (=Max({<Sales = {'>0'} >} Period))"} >} Sales)
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!
I'm sorry is the same response @anbu_cheliyan
Same as Anbu's answer - only returns the value for the accounts where the Period last traded is also the maximum period.
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.
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.