Various time periods depending on selection

[#1]

Hello,

I hope somebody can help me.

I am looking for a report showing various figures for a selected month and at the same time

1. sums of the selected month and the two month before (add the figures of three months) ("3 month rolling") and

2. sums of the selected month and the eleven month before (add the figures of 12 months) ("12 month rolling").

My idea is to set variables depending on the selected month or use the selected month in formulas to get to the 3-month figures and 12-month figures, but I did not find a way to do this. Is this possible ?

Any other (better) idea ?

Thanks a lot for your help.

Gunther

[swuehl]

Hi Gunther,

that's pretty easy using the so called set analysis / set expression.

With set expression, you could "change" the selection used for calculating your expression, but only for that calculation (i.e. change the context for that calculation only).

The syntax for a rolling 3 months would look similar to

sum( {<date= {"<=$(=max(date))>=$(=addmonths(max(date),-3))"} > Sales}

If you use additional fields describing your date like year, month, week and you might need to extend that a bit further.

Set analysis is a bit advanced but quite powerful. Please have a look a the manual and e.g. here first:

http://iqlik.wordpress.com/2010/09/11/the-magic-of-set-analysis-syntax-and-examples/

Regards,

Stefan

Nachricht geändert durch swuehl