CountOver Total for Pivot Table

[riley.anderson]

Hi,

I have a pivot table that has Dates as rows, and a DateDiff as Weeks as the columns for Responses.

I am trying to show Response Rate by Week of Send vs DateDiff from Sent.

The problem I’m having is that where there are gaps in responses for a specific DateDiff week then when I am trying to calculate % it goes against the Week total which is lower than the overall total.

You can see this from my Arena link here: Response Test Data

Basically my Perc Response vs Sent per Week is correct for each cell except the total for each DateDiff weeks. Whereas my Perc Response vs Sent Total is wrong for each cell except the total. I am using correct and wrong in the sense in what I am trying to achieve, not from what they are technically doing.

I need a way for the percentage to be responses vs total sent for that week, then total is also the total sent for that week, rather than a sum of all the rows below it.
E.g., Week 1 is calculating the total against 8,909 rather than the 10,000 I want it to.

Hope that makes sense, any questions and please just ask, many thanks.

Riley

[Xclipse]

Hi @riley.anderson, here is one approach – try creating multiple visuals. Split the total and the weeks to separate visuals.

Here is a good article on level-aware calculations:

Level-Aware Calculations: Tips and Best Practices

[Level-Aware Calculations in Amazon QuickSight] Level-Aware Calculations (LAC) are powerful functions that allow us to perform calculations at a granularity level which is different from the granularity of our datasets and/or visuals. The sample dataset referenced by this article is a dataset of product orders placed by customers. Each order can contain multiple products and each product in an order has a unique Line Item ID. Each row of our dataset represents one line item but we want to per…

[imcohen]

@riley.anderson - Instead of date diff for the fiscal week or calendar week, you can use truncDate(‘WK’, {your date field name}). You should be able to use countOver or sumOver for responses and for sent.

[Xclipse]

Hi @riley.anderson, did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!