cancel
Showing results for 
Search instead for 
Did you mean: 

Calculations at TWK using Calendar Week Attributes

kendw17
Explorer
0 Kudos

If a PO is past due, the earliest it can arrive is today + transit time. To do this today, I calculate in calendar weeks, convert to days and sum to TWK. The business doesn't like the rounding issue this causes. Is it possible to compare the start and/or end of a calendar week and a technical week. Example, IF calendar week = current week + TLEADTIME and calendar week start equals technical week start, rollup value, else....

kf1@wkprodloclocfr = IF( ("PERIODID4" = "$$PERIODID4CU$$" + "TLEADTIME@CALWKPRODLOCLOCFR") and ("$$PERIODID4CU$$START" = "$$PERIODID5CU$$START") ) , "HCBLOPENPOROLLFWD@PRODLOCLOCFR" , ....

or

("$$PERIODID4CU$$END" = "$$PERIODID5CU$$END") to load into beginning of month instead of end of month.

Accepted Solutions (1)

Accepted Solutions (1)

0 Kudos

Hello Ken,

The best way to achieve this would be to add a custom attribute in your Time Profile and fill it with a particular value for the periods where you want to perform your logic. It can be at the Technical week level or Calendar week level.
Add this attribute to your planning area and Planning levels of the key figures involved.
Then in your calculation, you can use this attribute's value to check for the existence of a particular period and execute your logic accordingly.
The only Con here is the additional effort to upload the value of the attribute with time profile upload.

Thanks

Gaurav

kendw17
Explorer
0 Kudos

Hi Gaurav,

I am having problems with the solution. Creating and updating the time profile wasn't hard. Adding the time profile attributes to the planning levels took some time.

I added the attributes to the time profile.

I added the attributes to lots of planning levels:

But the calculation didn't allow me to say "cbEndOfWeekTP@wkprodloclocfr" and the results are a run time calculation error.

CBLOPENPOROLLFWD@WKPRODLOCLOCFR = IF(("PERIODID4" = ("$$PERIDID4CU$$" + "TLEADTIME@WKPRODLOCLOCFR")), "HCBLOPENPOROLLFWD@PRODLOCLOCFR" * "CBENDOFWEEKTP", IF("PERIODID4" > ("$$PERIODID4CU$$" + "TLEADTIME@WKPRODLOCLOCFR"),"CBLOPENPURCHASEORDER@WKPRODLOCLOCFR",NULL))

0 Kudos

Hello Ken,

I am assuming your ask to know whether the current Calendar week is splitting between 2 months i.e. a split week in the background.

Now you have added CBSTARTOFWEEKTP and CBENDOFWEEKTP as attributes in your Time profile. You can upload any integer value in those attributes in the time buckets where there is a split week. Let's say you upload 1 in TW35a 2023 and 1 in TW35b 2023.

In your Calculation, while using these attributes, you do not have to specify the planning level, and "cbEndOfWeekTP@wkprodloclocfr" is a wrong syntax in calculation and will return always in error.
Instead, while writing your calculation at split week level, you check for a value of 1 in either of these attributes.

kf1@wkprodloclocfr = IF( ("PERIODID4" = "$$PERIODID4CU$$" + "TLEADTIME@CALWKPRODLOCLOCFR") and "CBENDOFWEEKTP" = 1 ) , "HCBLOPENPOROLLFWD@PRODLOCLOCFR" , ....

Let me know if your requirement was something else altogether.

Also, I figured you could also execute this logic without uploading custom attributes and using the values of the PERIODID attribute instead. If you want to do it without the upload, I can share the other logic as well.

Thanks

Gaurav

kendw17
Explorer
0 Kudos

Hi Gaurav, This worked great. Adding the attribute and adding it to the time profile was easy. I just set all values in TWK to 1. Then used filters to set the correct a and B values to zero. Adding the attribute to all the planning levels was the time consuming part.

I was able to get it to work two ways:

= IF("PERIODID4" = ("$$PERIODID4CU$$"+"TLEADTIME@WKPRODLOCLOCFR") AND "CBENDOFWEEKTP" = 1

resulted in the A TWK to have null and the B TWK to have the rolled up value.


"HCBLOPENPOROLLFWD@PRODLOCLOCFR" * "CBENDOFWEEKTP"

resulted in the A TWK having zero and the B TWK having the value.

kendw17
Explorer
0 Kudos

Hi Gaurav, Please share your other solution. It could save me days adding the time profile attributes to the 3 planning areas I would need updated.

0 Kudos

Hi Ken,

The other solution is based on the values stored in the PERIODID attribute in your time profile. If you have used the sample time profile provided with default values, there Technical Week A and Technical Week B will have different PERIODID values. So, we can PERIODSHIFT either of them by 1 bucket and subtract from the other to figure out if we are looking at a technical week or a full week.

ZCALWEEKCHECK@WKPRODLOC = "PERIODID4" - "PERIODID5"
Additional Inputs:"PROJECTEDINVENTORY@WKPRODLOC"

HZPERIODSHIFTCALWEEK@WKPRODLOC = IBP_PERIODSHIFT("ZCALWEEKCHECK@WKPRODLOC", -1)

ZSPLITTECHWEEKSTART@WKPRODLOC = "ZCALWEEKCHECK@WKPRODLOC" - "HZPERIODSHIFTCALWEEK@WKPRODLOC"

The Last key figure will give you the same result as you would have with a custom attribute in your time profile.

Regards

Gaurav

kendw17
Explorer
0 Kudos

That is very interesting. Shifting -1 results in a -1 on the B weeks. Shift +1 results in a 1 on the A weeks.

Thanks!

Answers (1)

Answers (1)

ayanbishnu1981
Active Contributor
0 Kudos

Hello kenanderson

Your below mentioned expression seems to work logically, but that thier is a syntax issue

kf1@wkprodloclocfr = IF( ("PERIODID4" = "$$PERIODID4CU$$" + "TLEADTIME@CALWKPRODLOCLOCFR") and ("$$PERIODID4CU$$START" = "$$PERIODID5CU$$START") ) , "HCBLOPENPOROLLFWD@PRODLOCLOCFR" , ....

Thier is no function "$$PERIODID4CU$$START" but you can use the "$$PERIODIDFR$$" and "$$PERIODIDTO$$" functions in your expression and I think it will work.

Please do let me know if you face any issue in the calculation

kendw17
Explorer
0 Kudos

Hi Ayan, I tried AND "$$PERIODID4TO$$" ="$$PERIODID5TO$$". The planning area activates but I get an SQL error when I try to return the KF in Excel.

kendw17
Explorer
0 Kudos

I did find the definition.

  • $$PERIODIDnCU$$: Fixed variable for the current period.

  • $$PERIODIDnFR$$: First period of the given planning horizon.

  • $$PERIODIDnTO$$: Last period of the given planning horizon