Hi,
I’ve attempted using runningSum and windowSum to get the results I am looking for but with no avail. Let me describe the problem statement
Let’s say we have the following fields daily_date, reporting_week, some_value_field (integer).
I am looking to make a calculated field such that we have a running sum from day to day that resets each week. For example some_value_field has the following values : 5, 10, 4, 5, 6, 1, 10, 4
This output would look like this for the calculated field
5, 15, 19, 24, 30, 31, 41 (week complete), 4 (we start again).
Is there a way to achieve this in QS? I am essentially creating a runningSum but it needs to reset each time the reporting_week changes. The data is daily, there are no irregularities or missing values.
Hi @gmikay,
would you be able to share the calculated field you currently have setup?
If you have a separate field in your dataset for ‘reporting_week’, you should be able to include that in the partition section, what if you tried something like the following:
But the numbers sum up downwards, so they do it at column_x level, if I remove this column_x (which are the rows), the numbers just repeat (i.e. value = calculated_value)
I think I am not being clear, my dates are the columns, so as we move to the right, we should see the calculated field value = previous value + the new value. In my case, they just stay constant when I don’t introduce a new column as row as you can see in my screenshot
If I add another column as a row, the numbers start summing downwards using this new column (instead of metric date).
I was able to get part of it to work using this formula where column_x is the rows of my chart; however, now as you can see, when reporting week changes, the values continue to sum rather than resetting, do we need to use windowSum instead?
runningSum(
sum({WK-1 Abs Delta}),
[{Reporting Week} ASC],
[ column_x]
)
In fact, if I replace reporting week with metric date, I get same results, so second argument has nothing to do with resetting the runningSum, I think I have to use windowSum here inside of the runningsum, what do you think?
In fact, you need to add the column you want it to reset in the last argument, so now the runnningSum continue for each metric date and resets at every reporting_week, column_x is the values you use as your row.