Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Guys,
i have the following data which i like to process in Qlik:
Name | Year | Month | Sales |
John Doe | 2022 | 1 | 1 |
John Doe | 2022 | 2 | 1 |
John Doe | 2022 | 4 | 1 |
Mara Doe | 2022 | 1 | 1 |
Mara Doe | 2022 | 2 | 1 |
Mara Doe | 2022 | 3 | 1 |
Mara Doe | 2022 | 4 | 0 |
I made a table out ouf it with Name in Dimension and Sales as Value (for each month a cloumn).
The Value column contains the set analysis:
Sum({<Month={"01"}>}Sales)/100
Then the Table look like this:
Name | Jan. | Feb. | Mar. | Apr. |
John Doe | 1 | 1 | 0 | 1 |
Mara Doe | 1 | 1 | 1 | 0 |
As you can see, in March it shows 0 for John Doe, although there is no Data for him in March at all.
Somehow Qlik generates automatically the zero, but i want to replace the "nonexsiting data" with "-".
I do not want to show a zero, unless there is Data (see table above) which conatins a 0-Value.
Unfortunately i can not use Pivot-Table for some reasons, but when i use it, it shows the correct result.
Now i want the same with a table:
Name | Jan. | Feb. | Mar. | Apr. |
John Doe | 1 | 1 | - | 1 |
Mara Doe | 1 | 1 | 1 | 0 |
What do i need to do?
Thanks for any advice!!
Sum() will return zero for a null value.
Use the zero format:
Num(Sum({<Month={"01"}>}Sales)/100, '0;0;-')
https://qlikviewcookbook.com/2014/05/the-third-format/
-Rob
Sum() will return zero for a null value.
Use the zero format:
Num(Sum({<Month={"01"}>}Sales)/100, '0;0;-')
https://qlikviewcookbook.com/2014/05/the-third-format/
-Rob
Hello Rob, thank you so much!! Now it works. I accidentally had field format 'numbers'. After i changed it into 'automatic' it started working 😀