hotw to obtain period of line item

Hello,

I'm stuck on a formula that seems obvious to me. I would like to retrieve the time period of a line item : 

Week : list formated.

time period by week : format time period, applies to week => i want to obtain time period for each week.

Szarek_0-1661759454019.png

Any idea?

thx for your time !

 

Best Answer

  • Szarek
    Answer ✓

    I have finally solved this 😉

    my data module for loan with pay day : 

    Szarek_5-1662989404887.png

     

     i have a calendar : 

    Szarek_0-1662988998801.png 

    Szarek_1-1662989009499.png

     

    I have a calcul module :

    Szarek_2-1662989062695.png

    Szarek_3-1662989081690.png

    My formula for "semaine3" is horrible but it works... If the pay day dont exist on the month (day 31 for february for exemple), it take the week of the last day wich exist of the month.

     

    And finally my module to convert interest from month to week : 

    Szarek_4-1662989371235.png

     

     

     

     

    My mind was traped by the system module i have make before for the week list, I wanted at all costs to use it to convert the day of a month into a week....

    Szarek_6-1662989553105.png

    Szarek_7-1662989570330.png

     

     

    thx for the help !

Comments

  • Hi Szarek, can you please add more details here, like what is the purpose of this mapping, is your model using weeks as time periods? How the blueprint looks like? Are you trying to get first week of the month for a reference? If yes, wouldn't it be better to have a time dime dimension that use weeks instead of months? OR have a separate System module where for each period by month you map Weeks, for example by getting period start date START() and using that date for finding week, but I think your model needs to be set with weeks in first place for that to work.

  • week is not used as time period. it's a list, because we wanted to customize them per year.

     

     

  • @Szarek  If I understood correctly you are in this situation: your initial data is input/calculated per Month (Time list) and you would like to have the same data in a week module where you have the mapping from your screen:

     

    Jan 22: Week 1

    Feb: Week 5 

    ....etc...

     

    If this is the case, then you should have the mapping in a system module, the other way around: a module with the Week List and the line-item as Time... 

     

    Week1:  Jan 22

    Week5: Feb 22

     

    In order to take data from the Initial data per month, you can use a Lookup.

     

    Hope it helps

    Alex

  • i will post the entire case, maybe this would be easier to explain , we want to make a cash budget by week.

    but most of the information to make budget are in month, that's why we work in month time.

     

    i have a system module to define the time period of each week (week are a list) :

    Szarek_2-1661782444330.png

    I have a module with the loans, and the day of the payment (list formated)

    Szarek_0-1661782315458.png

    I have a calculation module to "convert" day of month to the week

    Szarek_4-1661782829418.png

     

    i have a module with the amount of interest and capital by month

    Szarek_5-1661783217545.png

     

     

    and i want to obtain the result in the line item "test" wich have time YEAR and applies with week.

    The problem is the week S13 is defined on march 2022 in first module, but have the first april in too.

    so  i made the formula  :

    Rbt_interest[SUM: week, LOOKUP: SYS Semaine.Mois]

     

    so i wanted to replace the month of my first module by the  month of the week.

    i am not sure its very clear, hard to explaind and i am french 🙂

     

     

     

     

  • @Szarek Usually, the formula should be something like this: Rbt_interest[SUM: week, SUM: Year of Month]

    However, Time list is a special list that does not behave the same way as "normal" list, especially it is needed to change the granularity of the data. 

     

    If I understood correctly your case: you need to start from Data granularity is at Month Level and you want to arrive to a granularity of Year, Week level, where Month and Year are part of Time Anaplan list. 

     

    As previously stated, this can be achieved by changing the necessity to have SUM into LOOKUP:

     

     

    It is needed that every week/Year combination to have associated with the correct Month (see below picture in line-item "Rbt Month")

     

    alexpavel_0-1661947190327.png

     

    This is start Data granularity: 

    alexpavel_1-1661947309274.png

     

    And this is Data granularity: Year, Week using LOOKUP

     

    alexpavel_3-1661947403634.png

     

    Hope it helps

    Alex