To find current month growth w.r.t the last 3 months average

Q&A
, ,
1

Hello,

I have this dataset in quicksight - company_id , allocation_month and total_loans.
company_id is just a distinct id for a client.
allocation_month is a date which specifies to which month we are indicating. Example - 2024-12-01,2024-11-01, etc.
total_loans is the count of loans under that company.

Now I want to show the current_month % increase w.r.t the average of previous 3 months total_loans count.
How can I achieve this?

2

Hi @nishchay0001

Please refer the below Quick Sight documentation and community post this might be helpful for you.

3

Hi @Xclipse ,

Is there any other way to do this?
As after using these functions I am not getting correct output.
I guess there is some problem with the dataset, So If there is any other method please let me know.

4

Hi @nishchay0001

Could you please try the following calculations to get the desired result.

Note - Replace the fields from your dataset.

Previous 3 Months Loans

ifelse(
    truncDate("MM", {allocation_month}) >= addDateTime(-3, 'MM', truncDate("MM", now())) 
    and truncDate("MM", {allocation_month}) < truncDate("MM", now()),
    {total_loans},
    NULL
)


Average of Previous 3 Months

sumOver(
    sum({Previous 3 Months Loans}), 
    [{company_id}]
)/3


Percentage Increase

(sum({total_loans}) - avg({Previous 3 Months Loans}))/avg({Previous 3 Months Loans}) * 100
2 Likes
6

Hi @Xclipse ,

Thank you for the help. This method works.

1 Like