topic Re: Help in expression in QlikView

Help in expression

ali_hijazi — Tue, 06 Dec 2016 10:04:55 GMT

Hello got the following pivot table:

when the pivot table is expanded the results are correct; now if the user collapses the Month column then the opening balance will be 1053, and balance qty will be 387 for the first product, 1156 and 890 for the second item and 527 and 313 for the third

check the expressions in the attached QVW and please advise

Re: Help in expression

vinieme12 — Tue, 06 Dec 2016 11:19:55 GMT

Opening Balance Expression

if( dimensionality() = 4 , sum({<TrxType={'in','out'}>} Opening_Balance_Unit) ,

sum({<TrxType={'in','out'} , MONTH_NUM = {$(=MIN(MONTH_NUM))}>} Opening_Balance_Unit)

)

Balance QTY Expression

if( dimensionality() = 4 , sum({<TrxType={'in','out'}>} Balance_Quantity_Unit) ,

sum({<TrxType={'in','out'} , MONTH_NUM = {$(=MAX(MONTH_NUM))}>} Balance_Quantity_Unit)

)

Re: Help in expression

ali_hijazi — Tue, 06 Dec 2016 12:33:04 GMT

I did the same but on the field Month_YEAR but it didn't work

so it works on MONTH_NUM instead

what if the user collapsed all columns and wants to see by brand then the opening balance will be the sum of opening balance of all items on the min date; SO I DO AGGREGATION?

Re: Help in expression

sunny_talwar — Tue, 06 Dec 2016 12:43:41 GMT

It's seems to me that vinieme12‌'s response is already doing that? Isn't it?

Re: Help in expression

vinieme12 — Tue, 06 Dec 2016 12:46:01 GMT

No , work with the Dimensionality() value

For example

if( dimensionality() = 3 , // << if MonthNum collapsed, then min by Month

sum({<TrxType={'in','out'} , MONTH_NUM = {$(=MIN(MONTH_NUM))}>} Opening_Balance_Unit)

, //Else Sum

sum({<TrxType={'in','out'}>} Opening_Balance_Unit)

)

Re: Help in expression

ali_hijazi — Tue, 06 Dec 2016 12:56:11 GMT

ah yes