Custom Expression to filter out empty rows

[Michael Lis]

Hello,

 

I am trying to create a custom expression for summing and filtering a folder size column only if there is a string present in the Level column.

Then following this, I will set the Level column to property control to toggle between Level 1 and Level 2

This does not work for only selecting distinct values to add based on the Level Column : Sum([Folder_Size]) OVER (DISTINCT ((${piechartN}))

it sums everything else in the [Folder_Size] and adds it as blank. I only want it to Sum based on what is present in the Level_0, Level_1 or Level_2 folder.

Any ideas on this I tried the following expression below and received errors. Believe this can be done somehow

 

Sum([Folder_Size]) Over (DISTINCT ([Level_0]))

Sum([Folder_Size]) Over ([Level_0])

Sum([Folder_Size]) When (UniqueConcatenate([Level_0])

However, this is not working. Without a custom expression it is adding up all the other folder sizes that do not have anything listed in the level column.

Please any help is much apperciated. Please use the photos that help describe the process.

[Fabian Duerr]

In the properties of your pie chart go to the data section and use a data limiting expression:

${piechartN} is not null

This should automatically filter out all NULL values for whatever column you have selected

[Michael Lis]

${piechartN} is not null doesnt seem to catch the data to filter the data

[Colin Gray 3]

Adding the expression to the data limiting expression should work as suggested by fabd. Try removing your reference to the table name in your expression perhaps. Another thing to check is are your empty values definitely nulls It could be that they are blank strings. If so, you could try the expression:

[${piechartN}] is not null and [${piechartN}] != ""

in your data limiting expression instead

 

An alternative way that should also work is to use an expression like this in your Size expression for the pie chart:

Sum(If([${pieChartN}] is not null,[Folder_Size],0))

Where $pieChartN is your document property storing the selection of the level column. In the expression above I have nested an IF statement inside the SUM which replaces all Folder_Size values with 0 if the selected column (such as Level_0) is null. This should remove them from the pie chart. You should not need the OVER statement as the pie chart is effectively doing an OVER expression behind the scenes for you to create the pie segments.

[Jose Leviaguirre]

I have a different approach, but it requires some manual setup in case you do not want to unpivot your data. If you unpivot your data, you don't need step 1, but you need a "Level" column to identify what level you are talking about

1. add a calculated column to identify the level:

case

when [Level_0] is not null then "Level_0"

when [Level_1] is not null then "Level_1"

when [Level_2] is not null then "Level_2"

end as [Level]2. Add your column selection property LevelN

3. Limit your Pie Chart expression:

[Level] = "Level_0" or [Level]= "${LevelN}"