I am currently working on a SAP Analytics Cloud (SAC) story where I need to create a version-driven date filter. My requirement involves modeling values that require a dynamic reference date derived from the selected version. To provide some context, let's consider an example: If Version 1 is currently being worked on, the "first forecast month" might be June. However, if I select Version 2, which was from a few months ago, the "first forecast month" for that period might be February.
My goal is to filter the date dimension based on the selected version, allowing me to analyze data specific to a particular cutover period.
My version dimension is as below.
I have explored the available filter options in SAC (Analytics Designer too), but I couldn't find a straightforward way to link the version dimension with the date filter. Ideally, I would like the date filter to dynamically update based on the selected version, restricting the data displayed in charts, tables, and other visualizations.
My story:
Instinctively, I thought of using an attribute against the version members and incorporating them into a restricted measure. However, I discovered that attributes cannot be used in restricted measure definitions for dimensions like version and account. Even in the analytic application environment, it appears that attributes for dimensions such as version and account are not usable, while user-defined dimensions can have their attributes referenced.
I used filter line to do that. As in the below screenshot only user-generated dimension properties are shown.
Could you please share your expertise on how to implement a version-driven reference date filter in SAP Analytics Cloud? Are there alternative approaches or workarounds that could be considered?
- SAP Managed Tags:
Accepted Solutions (0)
Answers (3)
laykacc
william.yu
laykaccJust see if the below works for you. I am assuming you will maintain the attribute for month against version members like below
Now Create a calculated dimension as below. created 3 of them
Date Month : To convert the calendar month to number. 201803 (march 2018) will be converted to number 201803
ToNumber([d/"NA_SAP_CEP_SALES_PLANNING":CALMONTH].[p/CALMONTH])
Version Month: This will convert the cutoff month set for versions to numeric. I will use these to compare to derive before date, After date and On date.
ToNumber([d/"NA_SAP_CEP_SALES_PLANNING":Version].[p/STARTMONTH])
Now finally the Month grouping Bucket (Before Date, On Date and After Date) using the previous 2
IF([d/"DateMonth"]<[d/"VersionMonth"] ,"BeforeDate" ,IF([d/"DateMonth"]=[d/"VersionMonth"] ,"OnDate" ,"AfterDate" ) )
output:
Now use the MonthGroup Calculated dimension on columns and Version on rows.
Hope this helps !!
Please upvote/accept if this helps
Nikhil
Thank you for your suggestions.
In my case Forecast layout is not suitable. It forces the Time and Version dimensions to be present in the structure whereas I don't want to include Time dimension.
I would like to define a dynamic sum based on a reference date, as shown in the figure. The user will select the version, and the current date will be determined based on the selected version's attribute.
Lookup date X from user selected version;
Column 1: Sum of dates < x
Column 2: Value for date = x
Column 3: Sum of dates > x
Any alternative suggestions given the above example,please?
| User | Count |
|---|---|
| 76 | |
| 9 | |
| 8 | |
| 7 | |
| 6 | |
| 5 | |
| 5 | |
| 5 | |
| 5 | |
| 4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.