How Would I find the number of earned certificates in the first 60 days of companies launch?

Question & Answer
,
1

Hello Quicksight people. I am relatively new to your program and am hoping for help with a calculation.

I’ve been asked to find the number of certifications that each company has earned in their first 60 days.

I have two date dimensions: Company_Launch_Date and Cert_Date. Company_Launch_Date is the date the company became a customerr. Cert Date is the date the certification was earned.

I also have a Cert_ID (string dimension) which I’m hoping to be able to count to get the number of earned certifications in the 60 day time span

I’m hoping for one calculation that will show me how many certifications company x earned in the first 60 days that I can use for comparison and analysis.

Many thanks in advance!

2

Hi @Jeff2 and welcome to the QuickSight community!

To accomplish something like this, you may need to create 2 calculated fields but ultimately, one is just to be used within the other so you would only need to add 1 field to get your total.

What if you tried to run a dateDiff calculation to check if each row qualifies, it will mark as a ‘1’. Could look something like:

First 60 Days

ifelse(
    dateDiff({Company_Launch_Date}, {Cert_Date}, 'DD') <= 60 AND 
    dateDiff({Company_Launch_Date}, {Cert_Date}, 'DD') >= 0,
    1,
    0
)

Then, you can create another calculated field utilizing the one we just made and partitioning by your Client (Not sure what you’re field is titled like for that so you may have to adjust the formula below)

Total Certificates
sum({First 60 Days}, [Client ID])

Let me know if this works for your case or if you have any additional questions!

3

Thank you, Brett! I appreciate it

1 Like