Hi,
I have a line item formatted as date in the module like: "4/29/2020" and i want it to convert into "As of Apr 29, 2020". I tried myself with the below formula but didn't work, kindly advise:
"As of " & TEXT(MONTH(As of Date)) & "/" & TEXT(DAY(As of Date)) & "/" & TEXT(YEAR(As of Date))
Thank you!
Solved! Go to Solution.
Hi
There's no function in Anaplan that will give you the letters of a month.
Thankfully, the usual Anaplan flexibility is here:
- create a line item period with a formula PERIOD() of the date which will give you something like Apr 18
- create a line item "month letters" with the following formula: LEFT(NAME(period),3)
- then use this LI in your formula
See if this helps
Note: This is not as per best practices. You can break the formula string into multiple line items
This is not necessary - will be taxing to the model performance. Just break the formula as I said earlier you will be able to see the output
You can change the Time period from months to weeks in intermediate line item and then use that in final line item
@Misbah is correct in having that as long IF/THEN/ELSE statement is not good. I know I am late to the party, but think about doing the following:
Create a Month List with the code be the number of the month:
Create a SYS Month List Properties module with the following:
*Note - I am adding the "As of " and space after the month here because now the concatenation is only being done 12 times. Not only is this better, but it will lead to less "&" in the final result.
In a SYS Time - Year module (only dimensionalized by Time at the Year Level), have the following line items:
*Note - I am adding the comma and space here because in my model, I only have 2 years so the concatenation is only being done twice.
In a SYS Time - Day module (only dimensionalized by Time at the day Level), have the following line items:
So, you might be asking why am I breaking this up? There are a couple of reasons:
Hope this helps,
Rob