Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

kpatelacmm
New Contributor

Get a value one year back based on the current value

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

5 Replies

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

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

Not applicable

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

can you attach an example?

MVP
MVP

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

Dateofservice and DATE are 2 dates?

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

Highlighted
kpatelacmm
New Contributor

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

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.

kpatelacmm
New Contributor

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

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.