Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
Go to>Chart Properties> Presentation> un check the suppress missing option
Hi Massimo, this document can help you: Generating Missing Data In QlikView
Check the section about "Generating all combinations of several fields"
Hi,
This app will help u out.
in chart properties > Presentaion> uncheck supress zeros and null for both dimensions and expressions.
thanks,
bunny !
Hi,
Please try this expression:
=sum(if(isnull([UNIT PRICE USD]),0,[UNIT PRICE USD])*if(isnull(QUANTITY),0,QUANTITY))
Thanks,
Raghvendra
I do not have this option available in Presentation.
I can find something similar in Dimension but they are already unselected.
can you share your app
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.
Unfortunately data are sensible and I cannot share it. I think that example provided below to Ruben could help to understand.
try uncheck suppress when value is NULL for both dimension.
also uncheck suppress Zero- value in Presentation tab