Calculating correct percentages when changing time (day/week/month) for time series data

Question & Answer
1

Hi guys,

I have an application with requests (anzahl) and errors (anzahl_falsch) and would calculate a correct error rate (errors/requests). My dataset would have timestamped data but is currently imported from a database for testing purposes already coming with grouped aggregated values.

I created calculated fields and the summation of requests and errors is correct but unfortunately it is always follwing up with a summation of the values from each day once I switch from day to week in my time series diagram. I saw a few posts were people suggested to switch the column to average but that does not lead to correct values.

pct_falsch is calculated on database level per day and correct (only per day, aggregated is wrong)
cf_pct_anzahl_falsch = round(({cf_summe_anzahl_falsch}/{cf_sum_anzahl})*100,1)
cf_pct_anzahl_falsch = percentOfTotal(sum({anzahl_falsch})/sum(anzahl))

(had to omit values per day as I am a new user)

zeit per week:

image
image909×217 18.2 KB

correct values would be:
3/240 = 1,25%
20/349 = 5,73%
26/477 = 5,45%

Is there a way to ignore those groupings and have quicksight calculate errors/requests (anzahl_falsch/anzahl)?

I assume that is a challenge for everyone looking at time series diagrams. I changed this to the table format to debug where the error is actually happening but goal is to show time series diagrams with error percentages per day, hour, month and even hours for busier processes.

2

Hi @wulf. You may be able to accomplish this in SQL using a window function.

For example, SUM(anzahl_falsch) OVER(PARTITION BY zeit)/SUM(anzahl) OVER(PARTITION BY zeit)

3

Hi @Max,

tried to enter the sql directly in the calculated field but that did not work (as expected).

For unkown reason this worked today: round((sum({anzahl_falsch})/sum(anzahl))*100,2) (and Quicksight recognizing it as “custom” instead of “sum” or “average”.

Must have been an error on my end at some point.

My preference would be to rely on SQL and have the buttons in the visualization interact with the SQL instead of working with filters and parameters.

4

sum(anzahl_falsch)/sum(anzahl) doesn’t work?

5

It did not in the beginning but worked in the end. At first it was recognized as percent column and offered settings like “sum”, “average”,… and now is “custom”. I will see if I can reproduce it with the next visualizations.