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

19 Replies
MVP

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

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,

Min(Month) as MinMonth,

Max(Month) as MaxMonth

FROM table_name

GROUP BY Quarter ;

Regards,

Vijeta

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

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.