Hide columns or rows in visual without filtering them out

Question & Answer
, ,
6

Sorry, it’s not a visual for each calculated field. But a calculated field for each metric.
I use this for WoW and YoY % change metrics. Both of these metrics along with 6 other metrics (multi-week averages, YTD Totals, etc) are in a single visual. This is usually put into a pivot table. I’m not sure how well charts would work. But most have multiple calculated fields to determine their values.

For example, I want to display a WoW % change value. But it requires displaying the current and previous week.
To do this, I need to have 2 supporting calculated fields.
1 to calculate data in the “current” date range (we’ll call 1_week_previous)
AND
1 to calculate data in the previous date range (we’ll call 2_week_previous)

I do this using the ifelse function.

ifelse(
    truncDate("WK", {your_date_column}) = addDateTime(-1, "WK", truncDate("WK", now())
    ) , {your_then_value}, {your_else_value})

In the first section of the if statement, truncDate is confirming that you’re getting data for the previous week based on your dataset’s date column being equal to 1 week previous from the current date.
Then using the same formula, I create another calculated field to get the same data which is 2 weeks previous, (use -2, instead of -1).

  • for YoY calculations, I use -53 to get the previous year value.

For the calculated field I put into the visualization,
I use the following:

(sum({1_week_previous})-sum({2_week_previous}))/sum({2_week_previous})

I have no filters on this visualization and these calculations give me the correct values.
You might need to change some of it up for your needs. But I hope it works for you.

2 Likes