1 2 Previous Next 15 Replies Latest reply on Oct 17, 2018 2:52 PM by Shinichiro Murakami

# YTD Calc using DateDiff Function

I am in the process of calculating the YTD values using the Datediff function (see below screenshot). I am running into two issues:

• How to sum up the current month and previous month values?
• How to restart the calculation for every year.

Note - I need to use these YTD values in LOD calculations, so Table calculations, Window Sum etc. will not work. I have already tried them.

• ###### 1. Re: YTD Calc using DateDiff Function

Not exactly sure what is the final expected view, but anyways.

I don't know how July ~ October 2018 are handled when you say "YTD" ??

Thanks,

Shin

• ###### 2. Re: YTD Calc using DateDiff Function

Hello Shin,

Thank you for your prompt response. Please find below the screenshot with the expected result. As shown, YTD_PDAYS along Month of YearMonth is achieved using Table Calculations and YTD_PDAYS_NEW is calculated using DateDiff. You can see that the column from table calculation is summing up values. For Example February 2016 is sum of Patients days in January and February 2016, so on and so forth till Dec 2016 and it restarts at January 2017. This is the expected result. Request you to please let me help me achieve this using DateDiff calculation

• ###### 3. Re: YTD Calc using DateDiff Function

Why you need to use datediff?

Shin

• ###### 4. Re: YTD Calc using DateDiff Function

I need to use these YTD calculated values in LOD calculations. As I have used table calculations in YTD_PDAYS along Month of YEARMONTH, I am not able to use that field in LOD calculations. That is why I am trying DateDiff approach

• ###### 5. Re: YTD Calc using DateDiff Function

I am still not sure why you need to specify the method.

To get expected result, add year(YEARMONTH)

And running sums re-starting every year..

Thanks,

Shin

• ###### 6. Re: YTD Calc using DateDiff Function

Hello Shin,

As previously mentioned, I cannot use Table Calculations since I have to use the results from this calculation in LOD calculations

• ###### 7. Re: YTD Calc using DateDiff Function

Here you go.

This is the simplest.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 8. Re: YTD Calc using DateDiff Function

Thank you Shin! This method has helped me calculate the Patient Days Accurately.

Now when I use the same formula to calculate Membership, I do not get the correct values (See below screenshot). MEMB is the Monthly value for Baldwin Park, YTD_MEMB along Table is calculating the YTD Membership using Table Calculation and YTD_MEMB_NEW is using the calculation you suggested.

The reason it is showing such big numbers for Membership with the new formula is because in the filtered dataset I get 105 rows with 253,624 as the MEMB count. So it adds up all 105 rows. Is there a way I can tweak the formula to match the results from the Table Calculation

• ###### 9. Re: YTD Calc using DateDiff Function

Shin

• ###### 10. Re: YTD Calc using DateDiff Function

Hello Shin,

It doesn't let me attach my workbook again in this response. Do you have my Original Workbook?

Best Regards,

Ritu Chandiramani

• ###### 11. Re: YTD Calc using DateDiff Function

I am just lazy to try re-create your formula.

I have original but receiving from you is much easier.

You cannot attach file from inbox.

Shin

• ###### 12. Re: YTD Calc using DateDiff Function

Just as a workaround only for this specific case.

Create anther MEMB calc field.

Thank,

Shin

1 of 1 people found this helpful
• ###### 13. Re: YTD Calc using DateDiff Function

Not from inbox, but from original post.

Thanks,

Shin

• ###### 14. Re: YTD Calc using DateDiff Function

Hello Shin,

I have gone and marked your answer as correct on the forum.  Thank you for

I have a new issue with calculating Membership. May I send you the workbook

over email with problem explanation so that you can help me?

Or do you want me to post it on the Forum?

Best Regards,

Ritu Chandiramani

On Mon, Oct 15, 2018, 18:24 Shinichiro Murakami <

1 2 Previous Next