MTD based on Date Characterisitics

[Former Member]

Hi Team,

We got two date characteristics  i.e. Teco date and Labour date, based on the “Service order”.

We need to calculate MTD (actual and Prior year) by applying with this logic.

The calculation of MTD should be based on the Last TECO Date (Input Variable).

For example i.e. if I enter older date/month (not current date) [1/20/2014 means it should calculate for 1/1/2014 – 1/31/2014]

If the Input variable is current month’s date, then the MTD calculation should be = 1st day of month – Current Day.

For the above MTD calculation; we need to fetch the relevant Service orders and use it in the below final calculation.

The above same calculation logic should be applicable for MTD actual and prior year:

We don’t have any other Key figures involved except these date characteristics.

Final Calculation:

      If Last Labour Date is blank, 0, else Last TECO Date – Last Labour Date (by individual line item).

Can anyone help me how to achieve this requirement?

Thanks

San

[former_member182470]

Hi San,

Hope you are working with DBM data.

1) Create a CKF(Service Orders Count) with a constant value 1 and select "Counter of all detailed Values which are not null or zero" in Exception Aggregation with a reference char as "Service Order"

2) Drag any KF for dummy purpose and restrict it with Teco date(User entry based variable). This will enable variable input screen in the report. Hide this KF.

3) Drag CKF(created above) and restrict with a new variable which is replacing with Teco Date variable. You can follow my exclusive blog to do it. You have to read just month from teco date. Only 6 length. Details are in my blog. Eg: Teco date: 23.04.2014, then you should read just 201404(nothing but 01.04.2014 to 23.04.2014). This is your MTD. Your second scenario also will work with this same variable. Suppose user enters 20.01.2014, then it will take 201401(which is nothing but 01.01.2014 to 31.01.2014)

How to derive Calmonth & Calyear from Calday easily

4) You can simply offset -1 for Prior year. Make a copy of above MTD KF and just apply offset -1.

5) Convert your both Date chars into KFs with Formula variables(replacement paths)

6) Create one more New Formula for Date difference and put Last Teco Date KF - Last Labor Date KF

7) Finally create a grand formula like below:--

( Last Labor Date KF = = 0 ) * 0 + ( Last labor date KF < > 0 ) * Date Difference KF

Hope my reply helps you.

Regards,

Suman

[anshu_lilhori]

Are you going to show the count of service orders in that MTD column.

Please confirm.

Regards,

AL