how to calculate total while populating null as '-' in the expressions.

[#1]

Hi,

I have list of values which are missing or not present in database but is needed to be shown in the report as '-' (hyphen).

At the same time, need to calculate the total for those expression. But the total is not displaying correctly.

My expression uses : Value: aggr(sum(value),entity1,entity2) and Amount: aggr(sum(amount),entity1,entity2)

aggr has been added to show hyphen(-) for missing or null values.

for eg:

Expected Result

Entity1	Entity2	Value	Amount
a	aa	-	20
a	ab	10	40
a	ac	20	-
total		30	60

Actual Result

Entity 1	Entity 2	Value	Amount
a	aa	-	20
a	ab	10	40
a	ac	20	-
total		-	-

Any help would be great!!

[Anonymous]

Hi,

using pivot use the pick expression I posted. In chart properties navigate to Presentation, select Entity1 and check Show Partial Sums.  To understand dimensionality() just add it as expression. In this example your total row has a dimensionality = 0, when you have expanded your two dimensions the expression values in the chart have a dimensionaity of 2 (because of the 2 dimensions) if you collapse the second dimension the cell values will have a dimensionality of 1.

Best regards

Stefan

[#3]

Thanks Stefan , the above expression works!!

but my application has lots of expressions and totals. And at this time I cannot keep changing it in all the expressions.

Can you suggest some workaround or quick fix??

[#4]

Hi Stefan,

I am using your expression , but that expression doesn't work for derived columns.

for e.g.: my derived column is value-amount as expense, where if value is null or amount is null, then my derived column must also be null . but the above formula sums up all the values in the total which is incorrect..

thanks,

Harshala

[Anonymous]

Hi,

please post a screenshot of your chart and the expression you use.

Best regards

Stefan

[#6]

My expression is : if(sum(value)='' or sum(spend)='' or sum(percentile) = '',null(),if(dimensionality()=0, (sum(spend)-(sum(value)*sum(percentile))) ,( aggr(spend)-(aggr(value)*aggr(percentile)))))

[Anonymous · Accepted solution]

Hi,

the problem in your expression is that your first if condition doesn't evaluate your field values if the are NULL. '' returns an empty string. Secondly you Null if condition is evaluated before dimensionality. In the data you presented this first condition is always true because sum(xy) always returns a "minimum" of 0.

Try this instead:

if(dimensionality()=0,

aggr( if( not IsNull (Spend) and not isnull(Value) and not IsNull (Percentile), (sum(Spend)-(sum(Value)*sum(Percentile)))), Entity2) ,

( aggr(Spend, Entity2)-(aggr(Value, Entity2)*aggr(Percentile, Entity2))))

but I would reccomend to create flag in script which indicates if all three values in one row are not null (if your value fields are in one table). For example

if( not IsNull (Spend) and not isnull(Value) and not IsNull (Percentile), 1) as NotNullFlag

your expression in chart could than  be

if(Dimensionality() = 0,

(sum( { < NotNullFlag = {1} >} spend)-(sum( { < NotNullFlag = {1} >}value)*sum( { < NotNullFlag = {1} >} percentile))),

(only( { < NotNullFlag = {1} >} spend)-(only( { < NotNullFlag = {1} >}value)*only( { < NotNullFlag = {1} >} percentile)))

)

Given that your values return one value on level below totals.

depending on how much data you have this will be faster.

Best regards

Stefan