Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
Thanks Danielact, at the end I think this is the only way ....
You could try this:
if(YEAR=2010,sum(total{$<YEAR={2009}>}SALES),if(YEAR=2011,sum(total{$<YEAR={2010}>}SALES)))
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
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)))