I am trying to recreate the following fields in quicksight:
logit running avg
pd running avg on pd
pd running avg on score
I have added information on what is a field, and what is a calculated field. I need to somehow replicate the formulas but into quicksight, but i am having problem recreating.
Hopefully result would be:
End result is that i have a table in quicksight which is a replica of this. Reason for this is so i can then use the calculated fields in other visuals to show how the running avg of PD changes based on the running avg of credit score over time. This will be a line on a combo stacked bar chart, which would show the amount of customers, by date, grouped by credit quality, with the PD being compared.
Example of what i want (in excel), but need to replicate in quicksight:
mock data for help.xlsx
Hello @HarveyB-B,
Have you looked at our documentation on running average?
You mention you are having problems replicating them, can you tell us what issues are you encountering?
Thanks!
I have looked through the documentation, but i struggle using it. I think this is something i will improve in future as i gain more experience.
In terms of replicating the formulas from excel, into quicksight, i am struggling on what i should be writing in the calculated fields to perform the same calculations.
For the logit running avg, i need to do running average of ‘Logit’, by ‘agreementstartdate’ asc
For the pd running avg on pd, i need to do running average of ‘PD’, by ‘agreementstartdate’ asc
And then for pd running avg on score, im not sure what the calculation i should do to replicate in quicksight
In general, its just converting these into quicksight and the way i need to write the calculated field i struggle with. After viewing the excel, do you have any ideas?
Hi @HarveyB-B,
I just used Amazon Q in QuickSight to generate the first 2 with your descriptions and copied the formula from excel, I think this worked pretty well.
/*
(AI generated) running average of ‘Logit’, by ‘agreementstartdate’ asc
*/
runningAvg(sum(Logit), [AgreementStartDate ASC])
/*
(AI generated) running average of ‘PD’, by ‘agreementstartdate’ asc
*/
runningAvg(sum(PD), [AgreementStartDate ASC])
and for the pd running average on score just used the same formula as in the excel
1-(exp({logit_runavg})/(1+exp({logit_runavg})))
For what I can see it generates the same results as you can see in your excel.
Hope this helps!
Thank you so much, i think i was over complicating myself but this works perfectly. The one slight issue i have is that when i try to use the field:
1-(exp({logit_runavg})/(1+exp({logit_runavg})))
As a line as part of my visual, it does not like it. I assume its something to do with the format or the way the calculated field works, that it doesn’t allow it?
This is what i see when i try to use it as a line in a stacked bar combo chart
That does not happen to me with the mock data you sent, maybe there is some data in your dataset that makes the exp function overflow?
What i mean is that i want to use the running average on score as a line in my visual, opposed to a reference line. So i can see how the month-by-month average score affects the overall running score.
So my visual shows volume of cases (count) by date , grouped by credit quality. And then i have a line for month-by-month average, but then when i add the running average it doesn’t like it.
Edit: even when i try to add as a reference line, it shows the same thing. I will have a look and get back to you when i do
Hello @HarveyB-B,
Were you able to find out why you were getting this error and find a solution?
As we did not hear back from you i am marking this as a solution. Please let us know you need further help!
