Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
rachel_delany
Creator II
Creator II

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))

Anonymous
Not applicable
Author

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.