Calculate 18 month rolling

[#1]

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?

Thank you in advance for your help!!

Best regards,

Bettina

[#2]

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

Only(YEAR)

[#3]

Hi Magda,

thank you for your quik answer!

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

[#4]

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

[#5]

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

[Miguel_Angel_Baeyens]

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

$(=Only(YEAR))

Instead of

(=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.