Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

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

1 Solution

Accepted Solutions
Not applicable

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

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

19 Replies

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)

vijetasharma
Contributor III

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

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

sergey_maka8
Valued Contributor

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

If i catch your idea..

PFA

Not applicable

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.

Not applicable

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.

Not applicable

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.

Not applicable

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

sergey_maka8
Valued Contributor

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?

Not applicable

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.

Community Browser