19 Replies Latest reply: Apr 15, 2015 9:24 AM by Enikö Karlecz

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

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

• ###### Re: How to get the values of the first/last month relative to the quarter?

If you haven't selected any quarter, your Opening Balance for Min and Max Month both would be wrong.

Instead, you can use below two expressions for Min and Max Months..

SUM({<MONTH = {'\$(=Min(MONTH))'}>}OPEN_BAL)

SUM({<MONTH = {'\$(=Max(MONTH))'}>}OPEN_BAL)

• ###### Re: How to get the values of the first/last month relative to the quarter?

Hi. Thanks for the fast response. The pivot table still have zero values on QUARTERS 2,3,4 on min and 1,2,3 on max.

• ###### Re: How to get the values of the first/last month relative to the quarter?

Hi,

In Your script you can write like,

Min(Month) as MinMonth,

Max(Month) as MaxMonth

FROM table_name

GROUP BY Quarter ;

Regards,

Vijeta

• ###### Re: How to get the values of the first/last month relative to the quarter?

Hi. I was thinking of trying not to edit the script, but I'll check it out. Thanks.

• ###### Re: How to get the values of the first/last month relative to the quarter?

PFA

• ###### Re: How to get the values of the first/last month relative to the quarter?

Yes, that was the idea... almost. Do you know a way to make it dynamic? Like when I select different months, the values would, hopefully, also correspond to it. Oh, and thanks.

• ###### Re: How to get the values of the first/last month relative to the quarter?

So, if you select one month - for example 11 you should see only 4 quarter?

Is it correct?

• ###### Re: How to get the values of the first/last month relative to the quarter?

Hy Elli,

Simply write in the expression for

First Month Starting Balance :

Sum({\$<MONTH={1,4,7,10}>}OPEN_BAL)

Last Month Ending Balance:

Sum({\$<MONTH={3,6,9,12}>}CLOSE_BAL)

Hope this will help u out ....

Regards:

Hassaan

• ###### Re: How to get the values of the first/last month relative to the quarter?

Hi. Thanks. This solution was the same as Sergey's above.

Was thinking of making it dynamic. Like when I select different months, the values would change that corresponds to the selection.

• ###### Re: How to get the values of the first/last month relative to the quarter?

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.

• ###### Re: Re: How to get the values of the first/last month relative to the quarter?

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

• ###### Re: How to get the values of the first/last month relative to the quarter?

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.

• ###### Re: How to get the values of the first/last month relative to the quarter?

I use MAX(QUARTER) not MONTH

• ###### Re: How to get the values of the earliest/latest month relative to the quarter?

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

• ###### Re: How to get the values of the earliest/latest month relative to the quarter?

Elli Xi,

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

• ###### Re: How to get the values of the earliest/latest month relative to the quarter?

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.

• ###### Re: How to get the values of the earliest/latest month relative to the quarter?

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

• ###### Re: How to get the values of the earliest/latest month relative to the quarter?

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!

• ###### Re: How to get the values of the earliest/latest month relative to the quarter?

Hi Elli,

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