Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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
Entity2ValueAmount
aaa-20
aab1040
aac20-
total3060

Actual Result

Entity 1Entity 2ValueAmount
aaa-20
aab1040
aac20-
total--

Any help would be great!!

15 Replies
Anonymous
Not applicable
Author

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

Not applicable
Author

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??

Not applicable
Author

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
Not applicable
Author

Hi,

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

Best regards

Stefan

Not applicable
Author

total.png

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
Not applicable
Author

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