Running Percent Total in Pivot Table

[yipk]

Hi all,

I was wondering if you could help me out with getting the running % total on my QuickSight dashboard to work. I saw this thread about the same issue: Running percent of total in pivot table - #3 by charum

I tried the method of using runningSum() / sumOver(), but the final percentages at the bottom isn’t going to 100%. How can I fix this?

The equation that I am using is: runningSum(count({sort_center}), [{arrival_hour} ASC]) / sumOver(COUNT({sort_center}), [{arrival_date}])

[Max]

I think you need to partition by both the arrival_date on your running sum and the size_categories.

runningSum(count({sort_center}), [{arrival_hour} ASC],[truncDate(‘DD’,{arrival_date}),{size_category}]) / sumOver(count({sort_center}), [truncDate(‘DD’,{arrival_date}),{size_category}])

Let me know if that works!

[yipk]

Hey Max, thanks for the reply!

I tried doing this, but now I am getting some blank cells in the columns. Any ideas how to fix this?

[Max]

That becomes an issue of there not being any data to count. In order to fix that I would look to either add data via sql, or look to use lag functions.

How to fill in zeroes for dates with no values

Hello, I have an Athena dataset with users who have an email as their userid, and a lastSessionDate field for the last time they have signed in. I am plotting distinct_count(email) vs datediff(lastSessionDate, now()) to visualize how long users are spending between signing into our application. I am able to get this visualization working, but I can’t find a way to have the visual display zero for the dates where nobody signs in. I think I might be able to generate a generic series of dates (ei…

Counting empty cells and replacing with increasing values

thank you! yes it is something like mentioned. i will be trying this.I just want to add one thing that I have scientific data (sensor data) so no missing time (hrs) but missing observed values. (note: I couldn’t share data due to privacy issues)