topic Re: Is it possible.... by using set analysis in QlikView

Is it possible.... by using set analysis

Fri, 23 Dec 2011 13:36:35 GMT

IMPORTANT: I have attached an example

I am really sorry because in these days I am bothering the community with Set analysis problems.... but then again is it possible to obtain this table:

YEAR	SALES	SALES (YEAR- 1)
2009	754	-
2010	5	754
2011	30	5

Is it possible to obtain the previous values without modify the script (and WITHOUT using above or before)?

Thanks in advance

Re: Is it possible.... by using set analysis

danielact — Fri, 23 Dec 2011 15:19:37 GMT

I can't think of a way to do it without changing the script.

If you can change the script a bit, an easy method would be to create another table which doesn't link to your data source.

Years:

Load Distinct Year as Year2

resident Data;

Then, in your chart, use Year2 as your dimension, and use the following expressions:

sum(if(Year=Year2,Sales))

sum(if(Year=Year2-1,Sales))

Is it possible.... by using set analysis

Fri, 23 Dec 2011 16:13:43 GMT

Thanks Danielact, at the end I think this is the only way ....

Re: Is it possible.... by using set analysis

Fri, 23 Dec 2011 16:20:19 GMT

You could try this:

if(YEAR=2010,sum(total{$<YEAR={2009}>}SALES),if(YEAR=2011,sum(total{$<YEAR={2010}>}SALES)))

Re: Is it possible.... by using set analysis

swuehl — Fri, 23 Dec 2011 16:20:53 GMT

I would also prefer using a small script change, or above(), but since you don't want this, you could do the same like suggested by danielact with an synthetic dimension. Please see attached.

I assume you want to learn how to do this with set analysis. Well, the set expression is evaluated once per chart, so you can't request the actual dimension value YEAR for a row and use it in your set expression.

You can create set expressions for each year, though and get the same results, but you lose the flexibilty (if you add more YEARs, this chart won't reflect it automatically).

Regards,

Stefan

Re: Is it possible.... by using set analysis

johnw — Tue, 27 Dec 2011 21:30:43 GMT

I'd make script changes. But if that's not allowed, here's a way to do what frankrezee was suggesting without hardcoding the dates.

$(=concat(distinct 'if(YEAR=' & YEAR & ',sum({<YEAR={' & (YEAR-1) & '}>} total SALES)',',',YEAR)&repeat(')',count(distinct YEAR)))