Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

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

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.
Community Browser