Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
ericdanqua
New Contributor III

Derive last 12 monthends and last 4 quarters from max date

Hello,

I have this requirement that I need some assistance with.

Giving a MAX date of 11/feb/2019  I would like to deduce the last 12 monthend dates and the last 4 quarter dates . the expected outcome will be:

Thank You

Last12monthendsLast4Quarters
2019013131/12/2018
2018123130/09/2018
2018113030/06/2018
2018103031/03/2018
20180930 
20180831 
20180731 
20180630 
20180531 
20180430 
20180331 
20180228 

 

Tags (1)
Labels (2)
1 Solution

Accepted Solutions
Vegar
Valued Contributor III

Re: Derive last 12 monthends and last 4 quarters from max date

12 month ends:
SUM({<
DateField={"$(=DateStart(monthend(max(DateField),-1)))",
"$(=DateStart(monthend(max(DateField),-2)))",
"$(=DateStart(monthend(max(DateField),-3)))",
...
"$(=DateStart(monthend(max(DateField),-12)))"}
}> AMOUNT)

4 quarter
SUM({<
DateField={"$(=DateStart(quarterend(max(DateField),-1)))",
"$(=DateStart(quarterend(max(DateField),-2)))",
"$(=DateStart(quarterend(max(DateField),-3)))",
"$(=DateStart(quarterend(max(DateField),-4)))"}
}> AMOUNT)


Please ekskuse my Norglish and Swenglish typos.
1 Reply
Vegar
Valued Contributor III

Re: Derive last 12 monthends and last 4 quarters from max date

12 month ends:
SUM({<
DateField={"$(=DateStart(monthend(max(DateField),-1)))",
"$(=DateStart(monthend(max(DateField),-2)))",
"$(=DateStart(monthend(max(DateField),-3)))",
...
"$(=DateStart(monthend(max(DateField),-12)))"}
}> AMOUNT)

4 quarter
SUM({<
DateField={"$(=DateStart(quarterend(max(DateField),-1)))",
"$(=DateStart(quarterend(max(DateField),-2)))",
"$(=DateStart(quarterend(max(DateField),-3)))",
"$(=DateStart(quarterend(max(DateField),-4)))"}
}> AMOUNT)


Please ekskuse my Norglish and Swenglish typos.