topic SET ANALYSIS HELP in QlikView

SET ANALYSIS HELP

Sun, 13 Oct 2013 09:34:28 GMT

Good Morning to everyone,

I am trying to select the latest date for each raw in order to get the value on that date and the days before.

The problem is that articles in different raws have a different last date, and using the following code it picks the latest date of all the stocks which is good for 90% of the datas but not for them all....

How can I make it work for all the raws?

The code I'm using is:

sum( {$<Date = {$(=max(Date,2))}>}Value )

I also tried this:

aggr(NODISTINCT sum( {1<Date = {$(=max(Date,2))}>}Value ),article_code)

Before I was using this one without set analysis which works better but I get problems with non working days because on those days I don't get values.

sum(if(Date=Last_Date-1,Chiusura,0))

If it's not clear I'll send a sample file in a while but I need to clean it up as it's all in italian and big one so you would get lost in it!!

Thanks a lot for your help

Michele

Re: SET ANALYSIS HELP

lironbaram — Sun, 13 Oct 2013 09:54:23 GMT

set analysis won't work in this case because , what it does is simulating a selection in the model so it will always give just one value to whole model.

you can use firstsortedvalue maybe something like firstsortedvalue(Chiusura , -Date) and then , firstsortedvalue(Chiusura , -Date,2 ) and so on.

Re: SET ANALYSIS HELP

Sun, 13 Oct 2013 19:53:28 GMT

It works

Thanks a lot!!! I wasted hours not knowing that function

Re: SET ANALYSIS HELP

Mon, 14 Oct 2013 05:28:06 GMT

try with the help of variables

Re: SET ANALYSIS HELP

lironbaram — Mon, 14 Oct 2013 07:25:56 GMT

if this working

please mark the question as answered

to help other users