Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
is it possible to deal null values ?
I've the following pivot
Customer Sales_CurrentYear PreviousYear Difference
A 1000 800 200
B - 500 -
For customer B, I've a null value in current year and I can't calculate the difference.
Is there a function to manage this problem ?
Thanks in advance
How do you want to define the difference, if Sales_CurrentYear is unknown? Set the sales to zero?
IMHO, returning NULL in case of a unknown value is ok.
What expressions are you using?
I would compare the sales performance between the current year and year-1.
In the table above, the sales amount for Customer B in the current year is zero, it means a bad performance:
as value is -500 and in percentage we can consider -100%. How can I manage this null values ?
You are using three expressions in your pivot chart, right? How do they look like?
edit: if you are not already doing so, use an aggregation function like sum in your sales expressions.
Concerning total sales:
sum({<MY_YEAR={$(=Only(MY_YEAR))}, MY_MONTH= {"=$(=only(SALES_MONTH))"}>} GAINED_AMOUNT )
Concerning percentage
= IF (ISNULL(sum({<MY_YEAR={$(=Only(MY_YEAR))}, MY_MONTH= {"=$(=only(SALES_MONTH))"}>} GAINED_AMOUNT ) /sum({<MY_YEAR={$(=Only(MY_YEAR))}, MY_MONTH= {"<=$(=only(SALES_MONTH))"}>} VALOREACQM )),0,sum({<MY_YEAR={$(=Only(MY_YEAR))}, MY_MONTH= {"=$(=only(SALES_MONTH))"}>} GAINED_AMOUNT )/sum({<MY_YEAR={$(=Only(MY_YEAR))}, MY_MONTH= {"<=$(=only(SALES_MONTH))"}>} VALOREACQM ))
In my mind, I 'm checking if the sum(.values for customer...)/ sum(...total sales) is null; if yes I put 0, else I do the calculathion. But is there a better way to manage this issue ?
In order to make more simple the expression, is it possible to use the label (for example Total_sale) instead of the explicit calculation (sum (.....total sales....) ?
Having an application with three languages (for example English, German and Dutch) may I use a calculated label ?