Solved: How to show result from expression when it is null - Qlik Community

Report Inappropriate Content · ‎2015-10-14

Hi All,

My chart is quite simple: first column with customer, second column with products, third is the turnover.

Turnover is calculated by the expression sum([UNIT PRICE USD]*QUANTITY)

I would like to show the result through a "-" when the Customer never bought that specific product.

Expression automatically hides the value when sales and quantity are null (they do not exist because customer never bought).

I tried with IsNull but probably there is something I am handling wrong

if(IsNull(QUANTITY) or IsNull([UNIT PRICE USD]),'-',sum([UNIT PRICE USD]*QUANTITY))

Note:

Second column is populated with the list of all available product.

For what concern dimensions, I deselected "suppress when value is null" into chart properties.

but it does not work.

Any help?

rubenmarin · ‎2015-10-15

Hi, it was just an example of how to populate data, as you said, if you want to avoid the extra row you can check if the row exists:

Data:

LOAD *, Customer & '#' & Product as Key Inline [

Customer, Product, Sales

A,1,8

B,1,3

B,2,4

B,3,5

C,1,5

C,2,4

];

DataPopulated:

NoConcatenate LOAD Distinct Customer Resident Data;

Join LOAD Distinct Product, 0 as Sales Resident Data;

Concatenate (Data) LOAD * Resident DataPopulated Where not exists('Key', Customer & '#' & Product);

DROP Table DataPopulated;

You should work on the key to fit your real data, in example adding year and month to the key.

View solution in original post

avinashelite · ‎2015-10-14

Go to>Chart Properties> Presentation> un check the suppress missing option

rubenmarin · ‎2015-10-14

Hi Massimo, this document can help you: Generating Missing Data In QlikView

Check the section about "Generating all combinations of several fields"

Anonymous · ‎2015-10-14

Hi,

This app will help u out.

in chart properties > Presentaion> uncheck supress zeros and null for both dimensions and expressions.

thanks,

bunny !

raghvendrasingh · ‎2015-10-14

Hi,

Please try this expression:

=sum(if(isnull([UNIT PRICE USD]),0,[UNIT PRICE USD])*if(isnull(QUANTITY),0,QUANTITY))

Thanks,

Raghvendra

Report Inappropriate Content · ‎2015-10-14

I do not have this option available in Presentation.

I can find something similar in Dimension but they are already unselected.

avinashelite · ‎2015-10-14

can you share your app

Report Inappropriate Content · ‎2015-10-14

Hi Ruben,

thanks for the document. It is very helpful but I am not sure it helps for my scope.

I think I have to work on the expression...but I may be wrong.

Let attach the schematic table to better explain.

Products are listed in a separate file which contains all the products.

Value and customer come from a second file. There is only the list of products bought by a customer. (In this table could be not clear but a single customer could be present several times even with the same product if he bought them in different periods.)

TOTAL VALUE comes from the expression.

Report Inappropriate Content · ‎2015-10-14

Unfortunately data are sensible and I cannot share it. I think that example provided below to Ruben could help to understand.

Kushal_Chawda · ‎2015-10-14

try uncheck suppress when value is NULL for both dimension.

also uncheck suppress Zero- value in Presentation tab