Count two dates
I have 3 columns DueDate, FinishedDate, ID
What i want a metric of, is for a given time period, what is the ratio of finished date / due date
basically, in september, you had 40 records with a duedate in september, but only finished 20...
the x axis does not HAVE to be duedate or finisheddate, but if it's possible, the x axis should be duedate
i've tried the formula (count([Id]),[Months in FinishedDate])/(count([Id]),[Months in DueDate]) but it always just equals 100%... thoughts?
Hi liamcameron ,
If you're looking to calculate the ratio of finished dates to due dates for a given time period, here is an approach you can try:
Scenario 1: If you want to find the ratio of IDs that are finished in the same month as their due date (e.g., in September, you had 40 records with a due date in September, but only finished 20 in September):
(count([Id]), [Months in FinishedDate], [Months in DueDate]) / (count([Id]), [Months in DueDate])In this formula:
[Months in FinishedDate] = September[Months in DueDate] = September
Scenario 2: If you want to find the ratio of IDs that are finished any time after their due date (e.g., in September, you had 40 records with a due date in September, but only finished 20 anytime after the due date):
(count([Id]), [Months in FinishedDate], [Months in DueDate]) / (count([Id]), [Months in DueDate])In this formula:
[Months in FinishedDate] != N/A[Months in DueDate] = September
Make sure that your conditions for
[Months in FinishedDate]and[Months in DueDate]are correctly set according to your requirement. This will help you calculate the accurate ratio of finished tasks to due tasks.If you are trying to create a column chart, use 'months in due_date' as Category and use formula without due_date field.
Feel free to reach out if you have further questions, we're always happy to help 🙂
[email protected]
Paldi Solutions, Number #1 Sisense Plugins Developer
