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?

liamcameron

Posted 2 years ago·Last reply 2 years ago

3 comments

DRay

·2 years ago

Hello liamcameron ,

I wanted to follow up to see if the solution offered by Benji_PaldiTeam worked for you.

If so, please click the 'Accept as Solution' button so other users with the same questions can find the answer faster. If not, please let us know so that we can continue to help.

Thank you.

jpacheco

·2 years ago

Hello liamcameron

I wanted to follow up to see if the solution offered by Benji_PaldiTeamworked for you.

If so, please click the 'Accept as Solution' button so other users with the same questions can find the answer faster. If not, please let us know so that we can continue to help. 🙂

Thank you.

Benjamin Nissim

·2 years ago · Edited

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):

(<span class="hljs-built_in">count</span>([Id]), [Months <span class="hljs-keyword">in</span> FinishedDate], [Months <span class="hljs-keyword">in</span> DueDate]) <span class="hljs-operator">/</span> (<span class="hljs-built_in">count</span>([Id]), [Months <span class="hljs-keyword">in</span> 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):

(<span class="hljs-built_in">count</span>([Id]), [Months <span class="hljs-keyword">in</span> FinishedDate], [Months <span class="hljs-keyword">in</span> DueDate]) <span class="hljs-operator">/</span> (<span class="hljs-built_in">count</span>([Id]), [Months <span class="hljs-keyword">in</span> 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 🙂
inquiries@paldi.solutions
Paldi Solutions, Number #1 Sisense Plugins Developer