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

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!