
Re: how to calculate total while populating null as '' in the expressions.
Gysbert Wassenaar Sep 24, 2014 9:19 AM (in response to Harshala Rokade)You can use a format string to present 0's as hyphens. On the Number tab of the properties window of the chart choose the Fixed to option and use as format string #,##0;#,##0;

Re: how to calculate total while populating null as '' in the expressions.
Harshala Rokade Sep 24, 2014 10:09 AM (in response to Gysbert Wassenaar )Thanks Gysbert for the reply.
the change in format helps to show the hyphen but doesn't show the correct total.
in order to show total i have to use : sum( aggr(sum(amount),entity1,entity2)).
But applying sum turns all '' to 0's and then sums it up, which is not the expected output.

Re: how to calculate total while populating null as '' in the expressions.
Stefan Kunte Sep 24, 2014 10:24 AM (in response to Harshala Rokade)yes, and therefore you have to apply the not isnull part of the expression I posted. As aggr parameter you should use the field which contains the key for your lowest aggregation lvl of data.
Best Regards
Stefan



Re: how to calculate total while populating null as '' in the expressions.
Stefan Kunte Sep 24, 2014 9:36 AM (in response to Harshala Rokade)hi
this should work:
sum(aggr(sum( if ( not isnull(value), value)),entity1,entity2) and Amount: sum(aggr(sum(if not isnull(amount), amount)),entity1,entity2)
maybe you should skip entity1 as aggr parameter but this depends on your real data.
Best regards
Stefan

Re: how to calculate total while populating null as '' in the expressions.
Benjamin DEMOUSTIER Sep 24, 2014 10:33 AM (in response to Stefan Kunte)maybe with SetAnalysis and isNum(Value) condition
something like
sum(aggr({<isNum(Value)='1'>}entity1,entity2))

Re: Re: how to calculate total while populating null as '' in the expressions.
Harshala Rokade Sep 24, 2014 10:41 AM (in response to Stefan Kunte)yes, and therefore you have to apply the not isnull part of the expression I posted. As aggr parameter you should use the field which contains the key for your lowest aggregation lvl of data.
Best Regards
Stefan
Hi Stefan,
I tried your approach, but gives me correct total but it shows 0 values instead of '' , which is not the expected output.
Entity1 Entity 2 Value Amount a aa 0 20 a ab 10 40 a ac 20 0 total 30 60 
Re: Re: how to calculate total while populating null as '' in the expressions.
Stefan Kunte Sep 24, 2014 10:45 AM (in response to Harshala Rokade)did you use the whole expression including the if not isnull part?? Can you provide a sample app?
Best regards
Stefan


Re: Re: how to calculate total while populating null as '' in the expressions.
Harshala Rokade Sep 24, 2014 3:26 PM (in response to Stefan Kunte)Hi Stefan,
Sum of Rows is not available in Pivot table. I have to use Pivot table table instead of straight chart.
Also could you help me understand the dimensionality function used in the above expression.
Thanks,
Harshala

Re: how to calculate total while populating null as '' in the expressions.
Stefan Kunte Sep 24, 2014 3:37 PM (in response to Harshala Rokade)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

Re: how to calculate total while populating null as '' in the expressions.
Harshala Rokade Sep 25, 2014 4:42 AM (in response to Stefan Kunte)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??

Re: how to calculate total while populating null as '' in the expressions.
Harshala Rokade Sep 26, 2014 2:43 AM (in response to Stefan Kunte)Hi Stefan,
I am using your expression , but that expression doesn't work for derived columns.
for e.g.: my derived column is valueamount 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







Re: how to calculate total while populating null as '' in the expressions.
Stefan Kunte Sep 26, 2014 5:44 AM (in response to Harshala Rokade)Hi,
please post a screenshot of your chart and the expression you use.
Best regards
Stefan


Re: how to calculate total while populating null as '' in the expressions.
Stefan Kunte Sep 26, 2014 1:49 PM (in response to Harshala Rokade)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

