How to Date with SAP Analytics Cloud Part 2

Read Part 2 of the blog from Charles Braybrook to find out more about how to date with SAP Analytics Cloud.

Complex Date Filtering

Previously in How to Date with SAP Analytics Cloud , I took you through how to create Thresholds based on date selections. This time we are going to look at how to do Complex Dynamic Date Filtering (my terminology).

The concept behind this blog is to show you how you can report month-to-date data based on your selected date. Sounds simple I know, but there are always gotcha’s in everything we do, so let me explain.

To demonstrate how this works I’m going to use an example that I had to implement for one of our clients recently. The requirement was for a form of Month to Date filtering but with a caveat that we only show until the end of the previous week. The gotcha in this case is where we are in a new month and the week hasn’t completed in this month, then we want to show the previous month.

To illustrate what this looks like I will use the below calendar:

In the above month. If we are looking at the report from the perspective of Tuesday 8^th November, the report would bring back the current month up to Sunday 6^th November (End of previous week) based on Sunday being the last day of the week.

However, if we were looking from the perspective of Sunday 6^th November, the report would bring back the entire previous month (October) as the previous week ends in the previous month. Sound a bit crazy, stick with me and it will all become clear at the end.

So how do we achieve this?

Logically we would create an “if statement” but due to the complex and dynamic nature of this kind of filtering there isn’t a simple way of doing this. What we can do is use advanced filtering.

Advanced filtering allows us to create filters based on multiple dimension members, however by default it does not provide IF/ELSE functionality, it only provides “AND/OR functionality.” To get around this we need to create a flag and combine the flag with “AND statements” and by doing this we can effectively create an “if statement.”

We need to create what I am calling for the purposes of this blog a “Date Flag” by following the process below.

The first step in this process is to create a “Count Days” calculation:

This effectively gives us a measure that provides the number of days for a given selection.

Once we have the count days calculation in place, we need to create two measure-based dimensions. One dimension based on the number of days in a week and the other based on the number of days in a month. The reason that we create a dimension rather than a measure is that we want to keep this to a static number based on a selection.

So, with this in mind lets create “Days in Month”:

For the measure we use the count days calculation we previously created, and we check the “Use Measure Values as dimension members”.

Lastly, we add a Filter Context and for this we will use our Date and select by Range

In the Set Date Range pop up window, we want to setup a Current Date Input Control as shown below:

Now select the Granularity as Day as shown below:

Next under the range we change the Granularity as Current Month as shown below:

We now have our Days in Month dimension in place and now we create our Days in Week dimension in much the same was as Days in Month. The main difference is that instead of the Current Month Granularity selection, we select Week and toggle the Include range up to Current Period:

By doing the above, this means that when we select a particular Date, we have two dimensions that come back with two numbers. One for number of days in the month (up to current selected date) and another with number of days in the current week (up to current selected date).

Next, we want to create some “Dimension To Measure” calculations. The reason for this is that we need the value in our Dimensions to be a numeric so that we can create a calculation in our “Date Flag.”

To do this we create a Dimension to Measure based on Days in Month as shown below:

Keeping the Aggregation operation as SUM we do the same for Days in Week as shown below:

With this setup we can now create a “Flag Measure”. (Not quite the “Date Flag” more on this in a bit) and for this we create a calculated measure with the below formula:

The logic behind this is that if more days have elapsed in the current month than there are days in the current week then set the measure to 1 else 0.

The next step is to create our “Date Flag” Dimension. The main reason for creating a Dimension for this is that we can’t use measures in Advanced Filters.

This is very simply a Measure-Based Dimension on the “Flag Measure” we have just created:

 

Now we can set up our Advanced Filter as shown below.

Let’s break the Advanced filter down to explain what is happening here.

We have two AND conditions under an OR. Within each AND we include the Date Flag. We create the first as Equals to 1, (note that depending on what the Current Date selection is only 1 or 0 will be available to select not both). As this is looking at the Current month we want to add in AND Date is the Current Month. Lastly, in order to have it select only up to the end of previous week we add in an AND Exclude:

And set the date to be Current week.

This first condition basically means that if the Date Flag is 1, we include everything up to the end of previous week within the current month as we have enough days.

The second condition we are looking at is where the Date Flag Does not equal 1 and then Date is previous Month (using an offset on month) as we do not have enough days.

In order to show this working, I have created a simple Table only showing Dates that I applied the Advanced Filter to.

Using the example I talked about at the start, if we select Tuesday 8^th November in our Current Date:

Then we see the following in our Table:

Because there are more days in the Month (8) than there are in the current Week (2) the Date Flag will be set to 1 and the first condition in the Advanced Filter becomes active and we see the dates for the previous week.

If we now select Sunday 6^th November in our Current Date control, we get the following:

We can see that as there are more days in the current week (7) which includes Monday 31^st October than there are in the month (6), so the Date Flag becomes 0 and therefore the second condition is made true, and we show the previous month’s data.

Now that we have the logic in place, this gives us a foundation to make changes to the various components to allow for different data to be returned based the date selected.  For example, if we are looking at October 2022 and we select Monday 3rd:

The following data in the table below would be returned as we technically have a completed week.

However, we might not want to show the previous week in the current month if it’s just going to show the weekend and we can get round this by changing the “Flag Measure” calculation to the following:

Instead of Greater than 0, we have Greater than 2. Which effectively means we need at least 3 days in the previous week in order to include it for the current month. So now when we select 3^rd October 2022 we get the following:

There are several other scenarios we can satisfy by making changes to the various components which will return different results. Why not have a play around and see what you can do with it?

To meet up with our Data and Analytics experts, experience live software demonstrations, and to get hands on with the very latest SAP Analytics tools, register for Transformation NOW! 2023, taking place on 27th September in London.

Missed Part 1.  Read it here