How to get the values of the earliest/latest month relative to the quarter?

[#1]

Hi. I'm new to QlikView and needing help. My concern is that I can't get the correct values when there are no current selections.

I used the expression:

=Sum({$<QUARTER=P({1<MONTH={$(vMinMonth)}>}QUARTER)>}OPEN_BAL)

where:

vMinMonth =min(num(MONTH))

There's probably a syntax missing in the expressions used. Or is there a more practical way to do this?  I attached a sample file for your reference. Thanks in advance.

EDIT: Used these expressions:

=sum(Aggr(If(Min(TOTAL<QUARTER> MONTH) = MONTH, sum(OPEN_BAL)),COMPANY, QUARTER, MONTH))

=sum(Aggr(If(Max(TOTAL<QUARTER> MONTH) = MONTH, sum(CLOSE_BAL)),COMPANY, QUARTER, MONTH))

[#2]

Yes I do agree with sergey

So what month u select u ll get the result according to dat quarter in which dat month falls.

[SergeyMak]

So, you can use my previous solution if you additionally select month

or use new file

I've just add ,QUARTER={'=MAX(QUARTER)'} to set analysis

[#4]

Well, for example, if I selected only February and March (2 and 3), the minimum/earliest month of the first quarter should be the value 2. The values of the expressions should also change corresponding to those selections.

[SergeyMak]

I use MAX(QUARTER) not MONTH

[#6]

The values didn't change. Although, I can consider this if I can't find a dynamic solution. Thanks.

[SergeyMak]

Elli Xi,

Could you clarify with example what does it mean "dynamic", what number should change in which case in your opinion?

[#8]

Sorry, if I couldn't explain well. As stated above, if I selected 2 and 3 for the MONTH the values of the following expressions would change correspondingly. I fixed my earlier requirement from first to being the earliest month and last to latest. From the sample situation,  MONTH 2 being the earliest and 3 the latest.

[#9 · Accepted solution]

Was able to get these expressions:

=sum(Aggr(If(Min(TOTAL<QUARTER> MONTH) = MONTH, sum(OPEN_BAL)),COMPANY, QUARTER, MONTH))

=sum(Aggr(If(Max(TOTAL<QUARTER> MONTH) = MONTH, sum(CLOSE_BAL)),COMPANY, QUARTER, MONTH))

from http://community.qlik.com/message/370125#370125

[#10]

Hi Elli,

You Can try the another way which have mention the below attach Sample_QuarterMonth.QVW

[kavicsgym]

Hi, thanks for this, it helped me with similar problem.

Do you have any idea how to write the dynamic label for the Open Balance and Closing Balance expressions?

So within each quarter's label, the Min month (open bal) and Max month (close bal) would be shown relative to that quarter?

Thanks!