MTD issue using Customer Exit variables

[Former Member]

Hi Team,

We are facing an issue with MTD calculation based on the formula variables,

Issue: MTD Only (Actual and Prior Year) – show the same value for MTD. 

Requirement:

When the query is run for a selected month, the query should be run for that month based on the Last TECO Date (Input Variable).

• For example, dashboard is run for January 2014, the calculation of MTD should be based on the Last TECO Date : 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 dashboard is run for the current month, the MTD calculation should be on Last TECO Date = 1st day of month – Current Day

The above same calculation logic should be applicable for MTD prior year: instead of the input variable value, the value for previous year should be considered

Final Calculation:

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

Here we created two formula variables for last teco date and last labour date.

Also created 2 Customer Exit variable for MTD Actual calculation.

We have pulled the formula variable and CE variable into the selection and created a new CKF (Teco date).

We have done the final calculation on the basis of the ( (labour date ==0)*0 + last teco date-last labour date )for MTD ACTUAL.

MTD Prior Year-

when have done calculation on the basis of the( (labour date ==0)*0 + last teco date-last labour date )for MTD Prior year by giving offset -12 to the formula variable created.

Here for MTD Actual and MTD Prior year values are coming same  ,we tried with by doing customer exit variables as well, Still values are coming same.

We are not using any Key Figures just trying to use only the Date characteristics

please give your valuable inputs/suggestions to proceed for this requirement .

[former_member182470]

I suspect your input variable in filtering globally. I suggest you to restrict to a dummy KF(hide this KF) with input variable. After that check the report and let me know..

[Former Member]

Thanks Suman,  It is filtering Globally,

Could you please explain little more detail?

Thanks

[former_member182470]

I have made an exclusive blog for Dummy KF concept along with techniques. Please search below blog in this forum.

How to derive Calmonth & Calyear from Calday easily

[rathy_moorthy2]

Hi San,

If you are filtering globally on input variable, then the filter applies for all data in the query output. Hence it is showing data for the input month only, as it is getting filtered at a global level.

Please create a dummy kf as suggested above and in this restrict with the input variable.This restriction shall happen at a local rkf level, hence will not filter on the data coming for the complete query.

Also make sure that in the golbal restriction, you put a filter wide enough to bring 2 years data (if that is how the requirement is). Do not leave it wide open as then it will bring in all data from your info provider and that can impact the performance of the query.

hope it helps

Regards,

Rathy