Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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))
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))
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)
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
If i catch your idea..
PFA
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.
Hi. I was thinking of trying not to edit the script, but I'll check it out. Thanks.
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.
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
So, if you select one month - for example 11 you should see only 4 quarter?
Is it correct?
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.