I want running sum (week on week) information and have used windowsum function to calculate it. The only concern is for few weeks where I dont wanna have the running sum

[samzsura]

For instance,
W1 - 10, W2 - 20, W3-30 and … W40 - 400

I wanna represent W1, W2 and W3 as such, starting W4, I wanna have running sum.

[Thomas]

Hi @samzsura,
Welcome to the QuickSight community and thanks for sharing your question.

You should be able achieve your desired outcome by using a combination of ifelse, runningSum, and some additional aggregation functions. Going forward, it would always help to provide more context about the data structure and a complete example of input data and expected output. You can also use the QuickSight Arena to provide an example that others in the community can copy to provide a solution back to you.

I created a small example in which I added four values into the table that is grouped by week:

• Sales (Sum): Contains weekly sum of sales
• running_sum_sales: Contains weekly aggregated running sum of sales
• ‘partial_running_sum’: Contains weekly sum of sales for January 2020 and running sum (including January 2020) starting with February 2020
• late_starting_running_sum: Contains weekly sum of sales for January 2020 and running sum starting with February 2020 (excluding January 2020)

The result looks as follows:

The calculated fields have been defined as follows:

• running_sum_sales:

runningSum(sum(Sales),[{Order Date} ASC])

• ‘partial_running_sum’:

ifelse(
    min(extract('YYYY',{Order Date}))=2020 and min(extract('MM',{Order Date}))=01,
    sum(Sales),
    runningSum(sum(Sales),[{Order Date} ASC]))

• late_starting_running_sum:

ifelse(
    min(extract('YYYY',{Order Date}))=2020 and min(extract('MM',{Order Date}))=01,
    sum(Sales),
    runningSum(ifelse(
                    min(extract('YYYY',{Order Date}))=2020 and min(extract('MM',{Order Date}))=01,
                    0,
                    sum(Sales)),[{Order Date} ASC]))

I hope this gives you an idea of how you can achieve the desired behavior.

Did this answer your question? If so, please help the community out by marking this answer as "Solution!

[Brett]

Hi @samzsura,
It’s been awhile since we last heard from you, did you have any additional questions regarding your initial post?

If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.

Thank you!

[Brett]

Hi @samzsura,
Since we haven’t heard back, I’ll close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!