Filter Pivot Table

Question & Answer
,
1

My dataset consists of the following columns:

  • plan code
  • person id
  • quarter
  • amount

Sample data:

| ABC | 001 | 2023Q1 | 100$ |
| ABC | 002 | 2023Q1 | 100$ |
| ABC | 001 | 2023Q2 | 103$ |
| ABC | 002 | 2023Q2 | 103$ |
| DEF | 003 | 2023Q1 | 100$ |
| DEF | 003 | 2023Q2 | 150$ |
| GHI | 004 | 2023Q1 | 100$ |

Based on this data I’ve created the following pivot table:

| Company | 2023Q1 | 2023Q2 |
| ABC     | 200$   | 206$   |
| DEF     | 100$   | 150$   |
| GHI     | 100$   | -      |

My question: How can I add a filter to this pivot table so that only lines that might be in error are shown? A line is in error when

(a) the total amount for a certain quarter is significant different compared to the previous quarters (e.g. DEF), OR

(b) it has no value for a certain quarter (e.g. GHI)

Ivan

2

Hello @Ivan_Eulaers !

My suggestion would be to use a calculated field to determine whether the value is an error. You could probably use conditional logic.

Assuming your Quarter field is a string value, do you have a date field in your dataset? It would be easier to a date field so that you could say something like “If the values from this date to this date are greater than a specific range, then show me that date range in Quarters for a Clients”.

1 Like
3

I can turn this Quarter field into a date field.

But at the moment I’m concentrating on creating a calculated field in case no values are given in a specific quarter. But so far, this doesn’t work.

4

Hello @Ivan_Eulaers !

I’m sorry about the late reply, are you still working on this?

What did your calculated field look like that didn’t work? You could try something like the ifelse statement below:

ifelse(
({2023Q2} - {2023Q1} > [ThresholdValue]) OR isNull({2023Q2}), 
{Company},
NULL
)
5

Hi @Ivan_Eulaers. Checking in. We have not heard back from you regarding your question. We’d still like to help. We will archive this question. In case you need further assistance with your problem, please create a sample dashboard with sample dataset showing your problem state using Arena and please create a new post, so we can look at it from a fresh perspective. (Details on using Arena can be found here - QuickSight Arena).