How to use pivot totals to get an outcome (re-attempts calculation)

Question & Answer
1

I would like to confirm how I can use the outcome of a pivot table to get a summary for re-attempts.

  1. Customer passes first attempt, 1 customer ID, 1 check ID, 1 status of PASS
  2. Customer passes 2nd attempt, 1 customer ID, 2 check IDs, 1 status Fail then 1 status PASS
  3. etc…
    In our data we get results of a check for a customer. Customers can re-attempt (multiple checkIDs per customer)
    Pivot 1 - can get me the total count of checks by customer

Pivot 2 - using the total checks count per customer ID I want to now determine how many of those have “passed” which is Excel I just do a pivot on pivot 1 using the total count as the rows and get the number passed

image
image808×336 26.9 KB

I am struggling to replicate this in Quicksight… any suggestions?

2

I can’t add any of the other images but hopefully you get my question in Excel I can do a pivot on the output of another pivot, how can I do that in Quicksight?
I have tried:
Step 1: Calculate how many checks per customer {Calc_Count2Arg}
distinct_count({check_id},[{customer_reference}])

Step 2: Determine Pass rate (it doesn’t let me use “{Calc_Count2Arg}”
distinct_countIf({customer_reference},{Latest Status}=‘PASS’ AND {Calc_Count2Arg}=2)

/

distinct_count({customer_reference})

3

Hi @VanessaFierens,

It sounds like you are looking for how to calculate a count of the checks that have “passed” per customer. Based on your second post, it seems you have figured out how to calculate this but you are unable to reference your first calculated field in your second calculated field. Am I interpreting your situation correctly?

Can you please clarify why you are not able to use “{Calc_Count2Arg}” in your second calculated field in QuickSight? What is the error you are seeing?

To workaround not being able to use “{Calc_Count2Arg}” in your second calculated field, can you test replacing it with “distinct_count({check_id},[{customer_reference}])=2”?

4

I know I wasn’t very clear. But I was able to solve
Step 1 - distinctCountOver checkID by customer reference pre-aggregation
{Calc_Count2Arg_LAW} = distinctCountOver({check_id},[{customer_reference}],PRE_AGG)

Step 2 Group by the number of attemps and status
ifelse(
{Calc_Count2Arg_LAW}=0,‘No Attempts’,
{Calc_Count2Arg_LAW}=1 AND {trans_status}=‘PASS’,‘PASS 1st Attempt’,
{Calc_Count2Arg_LAW}=2 AND {trans_status}=‘PASS’,‘PASS 2nd Attempt’,
{Calc_Count2Arg_LAW}=3 AND {trans_status}=‘PASS’,‘PASS 3rd Attempt’,
{Calc_Count2Arg_LAW}=4 AND {trans_status}=‘PASS’,‘PASS 4th Attempt’,
{Calc_Count2Arg_LAW}=5 AND {trans_status}=‘PASS’,‘PASS 5th Attempt’,
{Calc_Count2Arg_LAW}>5 AND {trans_status}=‘PASS’,‘PASS >5 Attempts’,
{trans_status}=‘PASS_MANUAL’,‘Pass Manual’,
{trans_status}=‘REFER’,‘Needs Attention’,
{Calc_Count2Arg_LAW}>5,‘FAIL >5 attempts’,
‘FAIL’
)