5 Replies Latest reply: Nov 26, 2009 2:56 PM by Miguel Angel Baeyens de Arce

# Calculate 18 month rolling

Dear all,

I tried to calculate a sum of a value during the last 18 month, but I think I'm doing something terribly wrong. ;)

My code to calculate the value is the following:

`sum({<REPORTDAT_date = {'>=\$(=AddMonths(makedate( (=Only(YEAR)), '04', '01' ), -18))<=\$(=makedate( (=Only(YEAR)), '03', '31' ))'}>} REPORTNRCOUNT)`

If I select a year I receive only the calculated value of the selected year, not the value of 18 month. I'm using QlikView 8.5 and the editor always says that the expression is OK.

Could you please help me to find the cause of my error? I think it has to do something with the \$ sign?

Best regards,

Bettina

• ###### Calculate 18 month rolling

I'm not sure - but probably the problem is in

`Only(YEAR)`

• ###### Calculate 18 month rolling

Hi Magda,

This was also my thought. But I would like to query the selected year and calculate the 18 month rolling with the year the user selected in a list. Isn't this possible?

Best regards,

Bettina

• ###### Calculate 18 month rolling

hi bettina,

i have 12M, 6M and 2M rolling and just select one month and one year..........
for this solution i did a variable vPeriode12M: {\$<Year=, Month=, YearMonthRolling= {"<=\$(=Month + Year * 100) >=\$(=Month - 11 + if(Month <= 11, 12, 0) + if(Month <= 11, Year - 1, Year) * 100)"}-{"****00"}>}
(YearMonthRolling is a field (200901,200902 etc.) and {"****00*} is the YTD that i don't need for the rolling months!)
and the statement is sum( \$(vPeriode12M) figure)

regards,
corleone

• ###### Calculate 18 month rolling

I'm wondering about using this expression

`sum(if(Date>addmonths(today(),-18),Sales))`

I'm not sure if this expression is correct. But now this is only way wchich is on my mind

• ###### Calculate 18 month rolling

Checking your syntax it happens to me that you have to use

`\$(=Only(YEAR))`
`(=Only(YEAR))`
as you are using a function inside a set analysis (similar to which you are actually doing with \$(AddMonths...)

Just that little change and your expression will be fine.