topic Re: How to get the values of the first/last month relative to the quarter? in QlikView

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

Tue, 22 Apr 2014 05:52:13 GMT

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?

MK_QSL — Tue, 22 Apr 2014 06:07:41 GMT

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?

vijetas42 — Tue, 22 Apr 2014 06:12:30 GMT

Hi,

In Your script you can write like,

LOAD Quarter,

Min(Month) as MinMonth,

Max(Month) as MaxMonth

FROM table_name

GROUP BY Quarter ;

Hope this will help you.

Regards,

Vijeta

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

SergeyMak — Tue, 22 Apr 2014 06:21:05 GMT

If i catch your idea..

PFA

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

Tue, 22 Apr 2014 06:37:25 GMT

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?

Tue, 22 Apr 2014 06:38:00 GMT

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?

Tue, 22 Apr 2014 06:38:24 GMT

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?

Tue, 22 Apr 2014 06:42:48 GMT

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?

SergeyMak — Tue, 22 Apr 2014 06:48:12 GMT

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?

Tue, 22 Apr 2014 06:52:44 GMT

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?

Tue, 22 Apr 2014 06:56:23 GMT

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?

SergeyMak — Tue, 22 Apr 2014 07:06:32 GMT

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?

Tue, 22 Apr 2014 07:12:30 GMT

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?

SergeyMak — Tue, 22 Apr 2014 07:15:04 GMT

I use MAX(QUARTER) not MONTH

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

Tue, 22 Apr 2014 07:47:43 GMT

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?

SergeyMak — Tue, 22 Apr 2014 07:57:58 GMT

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?

Tue, 22 Apr 2014 08:21:31 GMT

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?

Tue, 22 Apr 2014 08:57:16 GMT

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

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

Tue, 22 Apr 2014 09:57:18 GMT

Hi Elli,

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

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

kavicsgym — Wed, 15 Apr 2015 13:24:13 GMT

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!