Technical Articles
SAP Analytics Cloud – Chop Up Your Dates Into Useful Parts
Recently A customer asked me where the MONTH() or YEAR() functions were in SAP Analytics Cloud (SAC), just like those in Microsoft Excel. These Excel functions allow users to return parts of a date so, for example, you can display your date by Week or by Quarter. In Excel we have the NOW() function that returns the date and time. See the following example:
We have the =now() function in B4 that returns the Date and Time but if we want to get ‘bits’ out of this we need additional functions. In E3, E4 & E5 we have functions that return the Year, the Month and the Week – in this case 2019, 12 and 50. In SAP Analytics Cloud we might want to do the same thing however there are no similar functions within SAC… or are there?
While we don’t have functions to do this we do have “properties” so let’s now look at the following scenario in SAP Analytics Cloud. I have dimension of type Date [Test v3:Date] and I want to see it as a Week Number or Month or Day or some other part, how do I chop it up? Easy, just create your chart as normal and instead of adding Date as the Dimension you create a new Calculated Dimension instead.
Select Calculated Dimension as the calculation type, name your new calculation and then add the following formula… Start with the Date Dimension name then type a full stop (point) then use the following key combination “Crtl+Spacebar” and this brings up the magic:
You can now define a Calculated Dimension for Month, Year, Quarter, Day even Day Name so a chart could them give you Sales by Day Name [d/”Test v3″:Date].[p/DAYNAME] even though the original data source only contains a date. There is a slight catch in that all Calculated Dimensions are returned as strings, this means that the daynames for example sort as strings not dates…
However a quick Custom Sort and away you go…
Daniel.
Thank you Daniel for this amazing post!!! I was lost trying to find the date formulas!!
thanks again!
This is good. We struggle in our project for these small calculations in SAC and ultimately had to do it in back end as specific columns (which our back end team never liked 🙂 )
Thanks for you blog we can use the above in SAC directly. Thanks again.
Hi Daniel,
I don't see create calculated dimension in my designer builder and also no hierarchy option seen for date dimension. My SAC version is 2020.14.11. Do i need to update my version or please let me know how can i see these options in my designer builder.
Thanks You.
Hi Daniel,
I am trying to pull the data from salesforce into SAC and I see that the date column comes in as a generic type and there is no way I can change that to a date type. I want to be able to parse this date into YYYY and MM but unable to do so. What you suggested can only be done on a date dimension right?
If I do a "ctrl+spacebar" I only get ID and Descr options and not Year and Month options like in your screenshot. I know probably this will not work on Dimension type but just want to confirm.Â
Thank you
Roopa
What is the data format of the file you are importing - Excel? CSV? could you edit the interim file to turn the data into dates? You are correct that you can only chop a date into date parts but maybe in the modeller you could make a date column from the data you have?
I am retrieving the data directly from Salesforce report in SAC. The column is date type in Salesforce but comes in as Generic in the model. In SAC there is no option for me to change the type ad that's why I was asking about this in this post. We tried using SPLIT function and that seems to work for now. Thank you.
I am using a live S/4 query as my data source and Calculated dimension option is not available with live data.
I like this, a nice simple, elegant solution. Thanks for posting, Daniel.