Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

morganaaron
Valued Contributor

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!

Tags (1)
9 Replies
ecolomer
Honored Contributor II

Re: Set Analysis Not Returning Expected Results?

try with:

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

morganaaron
Valued Contributor

Re: Set Analysis Not Returning Expected Results?

That simply returns 0 for every account unfortunately!

anbu1984
Honored Contributor III

Re: Set Analysis Not Returning Expected Results?

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

ecolomer
Honored Contributor II

Re: Set Analysis Not Returning Expected Results?

And now?

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

morganaaron
Valued Contributor

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!

ecolomer
Honored Contributor II

Re: Set Analysis Not Returning Expected Results?

I'm sorry is the same response @anbu_cheliyan

morganaaron
Valued Contributor

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.

MVP
MVP

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. 

morganaaron
Valued Contributor

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.

Community Browser