Delete Blank Rows in Table Chart

nkranker

Hello,

 

We have a drill down to a table chart. However it is showing rows that are blank and have no data to show. Is there any beast mode or alternative to get rid of this without scrubbing the data down since there are thousands of lines? I have attached an example of the table and what would need to be deleted (everything booked and GM) that has no data. Thanks!

guitarhero23

I think a different way might be the more proper way but this seemed to work. I recreated your dataset and added an extra one that had data in those last 4 cells

 

This formula assumes you DO WANT TO SHOW the row when any of the 4 values exist. Create the below Beastmode then use that beastmode as a filter where the criteria is that the field is >=0

 

(CASE
WHEN `2016-17 Booked Revs` IS NOT NULL THEN `2016-17 Booked Revs`
WHEN `2016-17 Booked Costs` IS NOT NULL THEN `2016-17 Booked Costs`
WHEN `2016-17 GP` IS NOT NULL THEN `2016-17 GP`
WHEN `2016-17 GM` IS NOT NULL THEN `2016-17 GM`
END
)

nkranker

Hi GuitarHero23,

 

Thanks, I did take your suggestion ans worked it with our current beast modes for 2016-17 revenue, costs, GM & GP. The last two are tricky since if GM or GP IS NOT NULL then it need to be the beast mode for th GM & GP as it's replacement since these two were manually calculated (not in the dataset). 

In addition, if I plug in what I have according to your suggestion, it states that I can't use aggregations in filter. Any other round abouts to get rid of the empty rows that no data exists for? 

 

(CASE
WHEN (CASE
when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Revenues CY`
when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Revenues CY`
End) IS NOT NULL THEN `Booked Revenues CY`
WHEN (CASE
when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Costs CY`
when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Costs CY`
End) IS NOT NULL THEN `Booked Costs CY`
WHEN SUM((CASE
when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Revenues CY`
when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Revenues CY`
End) -
(CASE
when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Costs CY`
when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Costs CY`
End)

) IS NOT NULL THEN SUM((CASE
when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Revenues CY`
when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Revenues CY`
End) -
(CASE
when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Costs CY`
when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Costs CY`
End)

) WHEN SUM((CASE
when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Revenues CY`
when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Revenues CY`
End) -
(CASE
when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Costs CY`
when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Costs CY`
End)
)
/
SUM((CASE
when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Revenues CY`
when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Revenues CY`
End)
) IS NOT NULL THEN SUM((CASE
when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Revenues CY`
when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Revenues CY`
End) -
(CASE
when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Costs CY`
when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Costs CY`
End)
)
/
SUM((CASE
when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Revenues CY`
when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Revenues CY`
End)
)
END
)

nkranker

Hi Thank you for you reply/solution. It's a bit more complicated since two of the four columns calculated are through beast modes. So if NULL I need to replace with that same beast mode. Unsure if this causes some circular error. The below formula was validated however when putting in as a filter, an error came up that filters can't have aggregations. Thoughts or ideas to get rid of theses empty rows with no data? Thanks!

 

 

(CASE

WHEN (CASE

when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Revenues CY`

when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Revenues CY`

End) IS NOT NULL THEN `Booked Revenues CY`

WHEN (CASE

when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Costs CY`

when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Costs CY`

End) IS NOT NULL THEN `Booked Costs CY`

WHEN SUM((CASE

when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Revenues CY`

when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Revenues CY`

End) -

(CASE

when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Costs CY`

when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Costs CY`

End)

 

) IS NOT NULL THEN SUM((CASE

when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Revenues CY`

when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Revenues CY`

End) -

(CASE

when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Costs CY`

when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Costs CY`

End)

 

 ) WHEN SUM((CASE

when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Revenues CY`

when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Revenues CY`

End) -

(CASE

when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Costs CY`

when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Costs CY`

End)

)

/

SUM((CASE

when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Revenues CY`

when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Revenues CY`

End)

) IS NOT NULL THEN SUM((CASE

when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Revenues CY`

when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Revenues CY`

End) -

(CASE

when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Costs CY`

when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Costs CY`

End)

)

/

SUM((CASE

when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Revenues CY`

when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Revenues CY`

End)

)

END

)