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: 
ttollin11
Contributor III
Contributor III

Set Analysis Qtr, max(Month)

I am trying to build an expression that shows the sum of the balances for the previous quarter's max month. Here's the variables and function I have:

previousQuarter = 'Q' & (Right(Quarter, 1) - 1)

maxMonthSelected =max([Deposit Month])

=sum ( {< Quarter = {$(=previousQuarter)}, [Deposit Month] = {$(=maxMonthSelected)} >} [Current Balance Amount] )

This is returning 0, which isn't correct. The variables themselves are returning what I expect except if Quarter = Q1, which I'll have to fix later. Am I missing something in my set expression? I've tried swapping the variables for the expressions of the variables and the syntax checker doesn't accept it.

Thoughts?

EDIT: Added (working...?) sample at Sunny's request.

Message was edited by: Tyler Tollin

1 Solution

Accepted Solutions
sunny_talwar

So, this shows you the right Month to display?

=Max({<Quarter = {$(=previousQuarter)}>} [Deposit Month])

May you you need Single/Double quotes within dollar sign expansion

=Sum({<Quarter = {"$(=previousQuarter)"}, [Deposit Month] = {"$(=maxMonthPrevQtr)"}>} [Current Balance Amount])

View solution in original post

8 Replies
sunny_talwar

May be try this

previousQuarter = 'Q' & (Right(Quarter, 1) - 1)

maxMonthSelected = Max({<Quarter = {$(=previousQuarter)}>} [Deposit Month])

=Sum({<Quarter = {$(=previousQuarter)}, [Deposit Month] = {$(=maxMonthSelected)}>} [Current Balance Amount] )

ttollin11
Contributor III
Contributor III
Author

Thanks for helping, Sunny.

I've either done something wrong, or it isn't working. I created a new variable:

maxMonthPrevQtr =Max( {<Quarter = {$(=previousQuarter)}>} [Deposit Month])

and inserted it into the original function:

=sum ( {< Quarter = {$(=previousQuarter)}, [Deposit Month] = {$(=maxMonthPrevQtr)} >} [Current Balance Amount] )

This is still returning 0. The new variable, if put into a textbox alone, works as expected.

sunny_talwar

So, this shows you the right Month to display?

=Max({<Quarter = {$(=previousQuarter)}>} [Deposit Month])

May you you need Single/Double quotes within dollar sign expansion

=Sum({<Quarter = {"$(=previousQuarter)"}, [Deposit Month] = {"$(=maxMonthPrevQtr)"}>} [Current Balance Amount])

ttollin11
Contributor III
Contributor III
Author

That variable does work as intended, yes. IE: selecting Q2 2017 returns month = 3.

I tried both single and double quotes, no luck.

sunny_talwar

Would you be able to share a sample to test this out?

ttollin11
Contributor III
Contributor III
Author

Sunny,

I added a sample application... which is working as you've described. I'll go back to my actual app and compare and see what's different and if I can fix it.

sunny_talwar

Sounds good

ttollin11
Contributor III
Contributor III
Author

I was able to get this working by putting my month and year fields in Num(). I'm still working on the =if() for when Quarter = Q1 and I need Q4 of previous year data.

Going to mark this as closed; thanks for you help as always, Sunny!