Set Analysis Qtr, max(Month)

[ttollin11]

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

[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]

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 · Accepted solution]

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]

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]

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]

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!