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…