topic Re: Pivot table total is incorrect for expression which is based if first exp is 0 then exp2. in App Development

Pivot table total is incorrect for expression which is based if first exp is 0 then exp2.

miranrai — Sat, 16 Nov 2024 18:28:49 GMT

I have a pivot table with one expression as:

If Expression1 = 0 or null then Expression2 else Expression1.

This is working perfectly fine but when it comes to total on the top its giving incorrect values as its evaluating only Expression1. My requirement is to get the sum of Expression2 and Expression1.

Any help please?

Re: Pivot table total is incorrect for expression which is based if first exp is 0 then exp2.

lironbaram — Wed, 17 Jun 2020 08:30:03 GMT

your expression work fine as it's evaluating the expression in the total row base on the whole data set

it's not doing sum of rows

if you want sum of rows

you can do

sum(aggr(if(Expression1=0,Expression1,Expression2),Dimension1,dimension2,etc..))

where instead of Dimension1 etc , you need to use the dimensions of the pivot table

Re: Pivot table total is incorrect for expression which is based if first exp is 0 then exp2.

miranrai — Wed, 17 Jun 2020 09:31:19 GMT

Thanks for your reply @lironbaram

I tried sum(aggr (..) in my expression but its giving me 0 value in total (I tried this expression in KPI). My actual expression in report is:

vRC is currency variable.

Sum(Aggr(if(
Sum({<DM_GC_RC.reporting_currency={'$(vRC)'}>}
DM_GC_RC.published_valuation_rc_amount)=0
or IsNull(Sum({<DM_GC_RC.reporting_currency={'$(vRC)'}>}
DM_GC_RC.published_valuation_rc_amount)),

sum([DM_GC.clean_value_gc])*$(vEXRateRC),

Sum({< DM_GC_RC.reporting_currency={'$(vRC)'}>}
DM_GC_RC.published_valuation_rc_amount)),
,[DM_RESULTS.security_group],[DM_RESULTS.instrument_type],[DM_RESULTS.GPV_Gross_Investment]))