Sales in the last 6 months per month

[xtrimf]

Hi,

How can I show the month dimention, so for each month it will show the last 6 months relative to current month dimention?

tnx

[xtrimf]

attached is a small example of what i am trying to achieve

[#3]

have you tried something like

month(datefield)-6

[xtrimf]

i tried

sum({<Month=>} if(date>=addmonths(Date,-5),value))

i tried also inside the set analysis but its not good cause it has to be evaluated per each month.

[xtrimf]

anyone?

[Oleg_Troyansky · Accepted solution]

I usually recommend a technique called "as of date":

1. Create 2 Date fields in separate tables (you might get by with Months instead of dates). One date/month field is associated with your transactions (call it "Transaction Month" for example). The other Date field will be used in the chart as a dimension.

2. Build a "link table" associating the two Month fields based on the required relation. In your case, you want to associate each "Display Month" with the 6 previous Transaction Months. (You can create multiple relations, if necessary - last 3 months, last 12 months, etc...). For each one of the relations, set a flag field = 1 when the Transaction Month falls in the required period compared to the Display Month.

For example:

Trans. Month     Display Month     Last6Months

Jan                         Jul                          0

Feb                         Jul                         1

Mar                         Jul                         1

Apr                         Jul                         1

May                         Jul                         1

Jun                         Jul                         1

Jul                         Jul                         1

...

3. In your chart expression, use a Set Analysis condition with the corresponding Flag:

Dimension = "Display Month"

Expression = sum ({<Last6Months={1}>}  Sales)

cheers,

Oleg Troyansky

[xtrimf]

I tried it but I coold not get it to work...can you please post an example?

[#8]

Have you tried using the above function?

above(sum(sales), 1, 6) this will return the sum of the 6 rows above the current row. I.e. the previous six month/weeks/days depending on your dimension.

[xtrimf]

the above function will not work for the first 5 months correctly cause the previous 6 months are not displayed...

[#10]

I'm not sure I understand you. You mean for month 1-6 the function will not work properly but for month 7-12 it does? That should not happen unless you've no data. In that case you can make an if statement to solve the issue.