topic Re: Set Analysis Not Returning Expected Results? in QlikView

Set Analysis Not Returning Expected Results?

morganaaron — Tue, 06 Jan 2015 10:29:45 GMT

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?

ecolomer — Tue, 06 Jan 2015 10:46:01 GMT

try with:

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

Re: Set Analysis Not Returning Expected Results?

morganaaron — Tue, 06 Jan 2015 10:51:43 GMT

That simply returns 0 for every account unfortunately!

Re: Set Analysis Not Returning Expected Results?

anbu1984 — Tue, 06 Jan 2015 10:53:39 GMT

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

Re: Set Analysis Not Returning Expected Results?

ecolomer — Tue, 06 Jan 2015 11:03:57 GMT

And now?

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

Re: Set Analysis Not Returning Expected Results?

morganaaron — Tue, 06 Jan 2015 11:05:37 GMT

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?

ecolomer — Tue, 06 Jan 2015 11:05:48 GMT

I'm sorry is the same response @anbu_cheliyan

Re: Set Analysis Not Returning Expected Results?

morganaaron — Tue, 06 Jan 2015 11:07:46 GMT

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?

tresB — Tue, 06 Jan 2015 11:15:46 GMT

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?

morganaaron — Tue, 06 Jan 2015 11:29:55 GMT

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.