Is it possible to hide pivot columns?

[janosch]

I have a pivot table that shows #notifications and the relative and absolute change from the previous quarter. The relative and absolute changes are implemented as calculated field:

periodOverPeriodDifference(distinct_count({reference}), {dateOfNotification})
periodOverPeriodPercentDifference(distinct_count({reference}), {dateOfNotification})

I would like to show only the current and previous quarter. This is how it looks like at the moment:

I would like to get rid of Q1 2022. The problem is if I change the filter on dateOfNotification to include only two quarters, the difference calculations in Q2 2022 do not show any results. This is probably because Q1 is needed to calculate those values.

Is there a way to base calculations on Q1 but do not show it in the pivot table?

[Peter]

Hi janosch,

This is possible with the following workaround.

First, you can leverage the periodoverPeriodLastValue function in both of your calculated fields. In addition, use the difference and percent difference funtions instead of the periodOverPeriodDifference and periodOverPeriodPercentDifference functions.

For example:

periodOverPeriodLastValue(difference(distinct_count({reference}), [{dateOfNotification} ASC],1), {dateOfNotification},QUARTER, 1)

periodOverPeriodLastValue(percentDifference(distinct_count({reference}), [{dateOfNotification} ASC],1), {dateOfNotification} ,QUARTER, 1)

Then filter on your {dateOfNotification} field to filter to the last 3 quarters.

In addition, filter on one of your calculated fields like so:

This should result in just the last two quarters displaying in your pivot table!

Please let me know if this workaround solved your issue.

Thanks!

[janosch]

Hi Peter,

I copy+pasted the new calculated fields, added them to the widget and added the filter. Q1 is not shown anymore, but the values for absolute and relative difference are wrong.

The values for Georgia and Germany should be zero, as there are no notifications in Q2.

The value for turkey/pepper are wrong. Absolute change is -29, relative change is -90,625%.

Also, the filter you proposed removes entries which have data only in Q3, like liquorice in the screenshot.

[Koushik_Muthanna]

Hi @janosch ,

Could you try the following method : https://democentral.learnquicksight.online/#Dashboard-TipsAndTricks-Calculation-Year-over-Year-Table-with-Filtering-aka-Date-Hider-

[Steph]

Hi @janosch, We hope Koushik’s solution worked for you. Let us know if this is resolved. And if it is, please help the community by marking his answer as a “Solution.”