Help in expression

[ali_hijazi]

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

[vinieme12 · Accepted solution]

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)

)

[ali_hijazi]

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?

[sunny_talwar]

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

[vinieme12]

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)

)

[ali_hijazi]

ah yes