Forum Discussion
Generating monthly table with diminishing Object values on hardcoded predefined values
Hello all,
My data contains alot of different object with yearly or monthly "contracts". These contracts represent the values of object on specific dates and whether the value deminishes on a periodic (yearly) or monthly basis. Example data:
For example: object 1 is worth 52.000 on 1-7-2020. On 1-8-2020 the value is the monthly depreciation between 52k and 41k. The problem is that the depreciation isn't the same during the start (1-7-2020) and the end date (1-7-2022). For the yearly contracts it's somewhat different. The value for object 2 on 1-1-2021 is 100K. A day later, 2-1-2021 it's 70K untill 1-1-2022. I'm having trouble generating a new table with values on a monthly basis. The output would be something like:
Could someone please help me out? Many many thanks beforehand.
Kind regards, Muffin
3 Replies
- amitchandak
Super User
Muffin92 , I did not get complete information. But this approach to create a new table can help
addcolumns(generate(Table, generateseries(0,4,1)), ("New Date" , Eomonth([Date], if([COntract] ="Monthly",([Value]*1)-1, ([Value]*12)-1) )+1) )
generateseries will decide the number of year or month
Thanks for the reply! Sadly i'm not able to get the output i'm looking for (screenshot 2 in post) with a value split between months since it's based on a single value. I can't make it work for multiple values (like object 1) or when the contract is different (yearly). Do you have some additional tips that could work with that?
Kind Regards, Muffin
