SAC - Week number calculation depending from "date input control" and "pc system date"

[adriano1973]

Hello everyone

I really need your help to calculate the number of weeks, between an "date input control" and the pc system date (of course this value should be recalculated every time I change the date in the "date input control").

Then I need to use this value (named as example WeekNum) to calculate the average of the turnover, that is AvgTurnOver = Turnover / WeekNum

So I have an input date control (upper left side of my printscreen), in this case Apr 2025, and I want to calculate a new measure that depends from this date in weeks since 01 Jan of the year of input date control.

In this case, if I count the weeks between 01/01/2025 and 30/04/2025 they are 17 and then I should use this value to calculate the average.

Suppose I change the date input control to Nov 2024, I need to have as result the number of weeks between 01/01/2024 and 30/11/2024.

How can I achieve this in a formula calculation?

Thank you for your support , I really appreciate your suggest

Adriano

[Ivan_C]

Hi Adriano,

At the time of writing, I don't believe that SAP offer any direct API access to the value of a Story Current Date input control. There are a few ideas that 'might' work, like getDimensionFilters(), but the fine print indicates that 'Currently, they don't contain time range filters', so no luck with this approach.

However. If you have a model in your story with a date dimension, you can use a hidden table to expose the value of the Current Date Input Control.

1. Create a table in the story. It doesn't need to have any dimensions in the Rows or Columns, in fact you can also apply a filter to it so that it returns no data at all for load speed.

2. Apply a date range filter to the date dimension in the model. Configure it to use the date imput control, and set it to the granularity of 'Day'.

3. Create a Text widget and add a dynamic text token that refers to the date dimension you assigned the filter to:

This will now display the formatted date selected in the Input Control. The downside is that it is held as a string and you can't expose the key, but the string can still be parsed in a script.

4. Create a script variable in the story. I have used 'WeekNum' defined as an integer public script variable.

5. Add a script to the onResultChanged() event of your working table (Table_1 in my case).

// Get the selected date as a plain text string from the Text_1 widget (e.g. "May 9, 2025")
var seldate = Text_1.getPlainText();

// Parse the string into a timestamp (milliseconds since 1 Jan 1970)
// Note: Date.parse returns NaN if the input is not a valid date string
var selectedDate = Date.parse(seldate);

// Get the current time in milliseconds
var todayMs = Date.now();

// Calculate the absolute time difference in milliseconds
var diffMs = Math.abs(todayMs - selectedDate);

// Convert milliseconds to full weeks (1 week = 1000ms * 60s * 60m * 24h * 7d)
var weeksBetween = Math.floor(diffMs / (1000 * 60 * 60 * 24 * 7));

// Store the result in a variable for use elsewhere
WeekNum = weeksBetween;

 6. Now that you have the elapsed weeks as an integer in a script variable, you can use this in, for example, a calculated measure:

Lastly, you can hide the working table and the text widget, just leaving the results as required.

If the date being a locale-based string is an issue, then an alternative is to get the date into the working table and then get it into the script using .getResultSet() on the table. To do this, you would need a model with a date dimension of granularity Day, and then turn on 'Unbooked' for the dimension. For getResultSet() to return data, you would also need to create a calculated measure that always returns data, so suggest just '1' in the calculation expression. This way, filtering the table by the date input control returns a single cell for the selected date and you can read the ID from this result.

Hope this helps.

Cheers, Ivan.