Hide columns or rows in visual without filtering them out

Question & Answer
, ,
1

I quite often encounter the problem that I need some data - for example for different dates - in a visual because I need them for a calculated field, but I do not want them to be displayed in the visual.

For example, I want to calculate the percent difference over one year for a value at the current date, but I do not want to also show the respective calculated value for the same date of last year in the visual (in this case a table). I cannot filter in only the current month because then the calculated field doesn’t work anymore - it needs the data for the previous year, too. But when I include both months, it also creates a column (or whatever) in the visual for the last month, which I don’t want to show.

Example for such a calculated field:

periodOverPeriodPercentDifference({value_for_selected_product}, {as_of_date}, MONTH, 12)

As I understand it, this is not possible. Please create a Feature Request for a feature to be able to hide values of a field in the visual without filtering them out / excluding them from calculations.

2

With an insight visual it seems to be possible to show a single value or several values without the need to filter out other values.

Example:

MetricComparison.fromMetricValue.formattedValue

But it is quite complicated, error-prone, doesn’t allow to use a table and probably does only work in some cases.

3

More generally, it should be possible to hide values from a visual without filtering them out, as the filtering might affect some calculations or totals or subtotals, etc.

4

I had a similar issue and my best solution was creating a calculated field for each metric. This was more work overall. But it allowed me to tailor each calculated field with it’s own filters instead of on filters for the entire dataset

5

@mgeurian

I’m not sure how your solution looks like.
In your solution, you have a different visual for each calculated field, which allows you to have different filters for each visual/calculated field?

So you would need a visual for each metric and cannot put them in one visual, for example a table or bar chart.
But then you still would have to do without bar charts or tables, as you cannot include values in the calculations which you do not want to have shown in the table or bar chart (but maybe an insight would allow this).
Is my understanding correct?

My problem is not so much that I would need different filters for the different calculated fields, but rather that I want to include (values for) some dates in the visual, but omit other dates which I nevertheless need for the calculations of the values for the remaining dates.

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
7

Thanks a lot. It looks helpful.

And with a parameter, it should be possible to get such results not just for the current date, but an arbitrary date the user can select via a control - but not via filtering (which would exclude dates needed for the calculated fields).

1 Like
8

What’s important with the two interim values you need in order to calculate the percentage value:

When you also put them in the value columns in the same table like the last calculated value, you have to sum over them to get shown the correct results. At least when you have other “dimensions”, it seems. At least, I get wrong results when I use other aggregate functions like Max or Average for those values in the table.