Hide rows in chart which are empty / display null values

Question & Answer
, , , , ,
1

Dear Quicksight Community,

can you please tell me how to hide all cells with the value null in my pivot table?
I don´t know why there are shown null values anyway after the cells were empty the previous months before.
For my case it would help very much if there is a null or an empty cell for the whole row in my chart to completely hide this row.

Any ideas how to solve this?

image
image960×357 18.2 KB

Best Regards

2

Hello there,

Please see the older post with similar issue Dealing with "empty" cells in QS - #2 by darcoli

May be you get some idea to filter out null/empty

hope this helps.
Cheers,
Deep

3

thanks for the hint, but I don’t want to rename the empty / 0/ zero cells.

Simple example:
I want to create a report for a year on a monthly basis for employee salary components.
If there is no cost for a salary component in a month, the cell should be blank or show a “0”.
If there is no cost for a salary component in all 12 months of the chart, the salary component should not be shown at all for the employee.
There is no need to display this salary component if there are no costs for the entire year.
If there are costs for the salary component in at least one month the component should be displayed.

Any idea to solve this?

Why is there sometimes a 0 in the cell and why are cells without values sometimes empty?

Thanks for the help

4

Hi @de.schmidd
are these lines also within your dataset?
Are there any calculations on the shown fields?
Looks like there is something in the data. Because in Feb are no zeros and in Mar are.
Did you already check the decimals? Maybe it is a 0,000000001.
BR

2 Likes
5

Hello @de.schmidd, I think I understand how you would find a solution for this. You may need to alter my suggested calculations slightly depending on how you are grouping the salary component and also whether it is an actual 12 months or a whole year. I’ll try to explain it so making changes will be simple.

Get your year date:
year = truncDate('YYYY', {date})

To manage the last 12 months instead of this year:
last12 = ifelse(addDateTime(-12, 'MM', now()) <= {date}, {salary}, NULL)

Create an ifelse statement that will check if your annual salary is greater than 0, if so return the value, else don’t:
ifelse(sumOver({salary OR last12}, [{year}], PRE_AGG) >= 0, {salary}, NULL)

Then you can use this value in your pivot table and use the custom filter to exclude nulls. Also, if you need to group by another category like company, product, etc, add that into your partition field with [{year}].

I will mark this as the solution, but if you have any follow-up questions on implementation, please let me know!