topic Re: Set Analysis Qtr, max(Month) in QlikView

Set Analysis Qtr, max(Month)

ttollin11 — Tue, 19 Jun 2018 14:27:32 GMT

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

Re: Set Analysis Qtr, max(Month)

sunny_talwar — Tue, 19 Jun 2018 16:59:51 GMT

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

Re: Set Analysis Qtr, max(Month)

ttollin11 — Tue, 19 Jun 2018 17:18:24 GMT

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.

Re: Set Analysis Qtr, max(Month)

sunny_talwar — Tue, 19 Jun 2018 17:21:10 GMT

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

Re: Set Analysis Qtr, max(Month)

ttollin11 — Tue, 19 Jun 2018 17:27:13 GMT

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

I tried both single and double quotes, no luck.

Re: Set Analysis Qtr, max(Month)

sunny_talwar — Tue, 19 Jun 2018 17:31:19 GMT

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

Re: Set Analysis Qtr, max(Month)

ttollin11 — Tue, 19 Jun 2018 19:50:50 GMT

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.

Re: Set Analysis Qtr, max(Month)

sunny_talwar — Tue, 19 Jun 2018 19:55:25 GMT

Sounds good

Re: Set Analysis Qtr, max(Month)

ttollin11 — Thu, 21 Jun 2018 12:30:45 GMT

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!