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

[MK_QSL]

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)

[vijetas42]

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

[SergeyMak]

If i catch your idea..

PFA

[#5]

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.

[#6]

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

[#7]

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.

[#8]

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

[SergeyMak]

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

Is it correct?

[#10]

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.