topic Re: Get a value one year back based on the current value in QlikView

Get a value one year back based on the current value

kpatelacmm — Mon, 23 Mar 2015 17:51:18 GMT

Current date = 201503

I am trying to get the Bonus Amt and MM one year back based on the current date. I tried the formula below but its not working.

=sum({$<Month= ,YEAR= ,Rolling12= ,QUARTER= ,DateofService=AddMonths(DATE,-12)>}Bonus_Amt)
/
=sum({$<Month= ,YEAR= ,Rolling12= ,QUARTER= ,DateofService=AddMonths(DATE,-12)>}MM)

Thanks

Re: Get a value one year back based on the current value

sunny_talwar — Mon, 23 Mar 2015 18:00:30 GMT

Not entirely sure if the formula is correct or not, but the syntax is definitely something which I can help you fix:

=Sum({$<Month= , YEAR= , Rolling12= , QUARTER= , DateofService= {'$(=AddMonths(DATE,-12))'}>} Bonus_Amt)
/
=Sum({$<Month= , YEAR= , Rolling12= , QUARTER= , DateofService={'$(=AddMonths(DATE,-12))'}>} MM)

HTH

Best,

Sunny

Re: Get a value one year back based on the current value

Mon, 23 Mar 2015 18:39:29 GMT

can you attach an example?

Re: Get a value one year back based on the current value

maxgro — Mon, 23 Mar 2015 19:03:38 GMT

Dateofservice and DATE are 2 dates?

=Sum({$<Month= , YEAR=, Rolling12=, QUARTER=, DATE=, DateofService={'$(=date(addmonths(max(DATE),-12),'DD/MM/YYYY'))'}>} Bonus_Amt)

Re: Get a value one year back based on the current value

kpatelacmm — Mon, 23 Mar 2015 19:33:34 GMT

Both are the same date just different format.

DateofService is yyyymm format. DATE is in mm/dd/yyyy format so I was converting the date format.

Re: Get a value one year back based on the current value

kpatelacmm — Mon, 23 Mar 2015 21:10:33 GMT

Attached is an example.

Basically I have months going across. If you look at the PCP Bonus 201407 to Current is zero. What I want to do is be able to look back one year so for example for 201407 look back one year to 201406 and see the PMPM or 1.55 then use that 1.55 and multiply that by 2622(201407 MM). But it needs to look back one year for each of the column where the PCP Bonus is zero.