Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Calculate sum of variances for last quarter max month

Hi there,

I want to create a calculation which sum last moth of previous quarter.

Create calculation as below but it is not working might be I have variable contains another variable inside. Given below field and variables.

MonthYear = Month(business_date)&Year(business_date)

Previous_QTD_Actionable_Variances=    Sum({$<MonthYear={"$(vPreviousQuarterMaxMonth)"}>} ipt_actionable_total)

vPreviousQuarterMaxMonth=     MONTH(QuarterStart(vMaxBusinessDate)-2)&YEAR(QuarterStart(vMaxBusinessDate)-2)

vMaxBusinessDate=      =DATE(MAX(business_date))

Really appreciate, if someone can help.

2 Replies
Highlighted
Creator
Creator

Re: Calculate sum of variances for last quarter max month

Try something like this:

Previous_QTD_Actionable_Variances = Sum({$<Date(MonthEnd(business_date))={"$(vPreviousQuarterMaxDate)"}>} ipt_actionable_total)

vPreviousQuarterMaxDate = DATE(QuarterStart(vMaxBusinessDate)-1)

vMaxBusinessDate = DATE(MAX(business_date))

Highlighted
Contributor
Contributor

Re: Calculate sum of variances for last quarter max month

Thanks Rachel for quick response.

It did not work out.

I am not sure why $<Date(MonthEnd(business_date))={"$(vPreviousQuarterMaxDate)"}>} is not comparing, if I run them individually, it returns correct value.

I tried hardcoded Date(MonthEnd(business_date)) = '06/30/2017' then it compares with others and return full sum.

when I try to harcoded {"$(vPreviousQuarterMaxDate)"}>} = '06/30/2017' it throw an error while compiling.