topic Calculate 18 month rolling in QlikView

Calculate 18 month rolling

Thu, 26 Nov 2009 17:13:49 GMT

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

Calculate 18 month rolling

Thu, 26 Nov 2009 17:29:04 GMT

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


Only(YEAR)

Calculate 18 month rolling

Thu, 26 Nov 2009 17:36:26 GMT

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

Calculate 18 month rolling

Thu, 26 Nov 2009 19:14:37 GMT

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

Thu, 26 Nov 2009 19:49:49 GMT

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

Miguel_Angel_Baeyens — Thu, 26 Nov 2009 19:56:15 GMT

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.