Cumulative months

Hi
I should create a line chart that shows me in the x axis the months (from January to December) and in the y-axis the cumulative calculation with respect to the various years.
this is to see how a month has performed compared to various years

attached an example done in excel.

how do i play it on quicksight? what calculated field can i use?

Hi andreab!

Could you give some more detail on which cumulative value you’re attempting to calculate?

I ask because depending on if you’re trying to calculate a basic count/sum or a distinct count it’s either simple or more complex. If you’re not doing a cumulative distinct count you can check out the RunningSum function.

Hi Rachel

with runningsum, however, I will have on the x-axis the date that goes on over time

on the x axis I want to have the 12 fixed months (whatever the year) while on the y axis I want to see the various values ​​of the month (x axis) with respect to the different years

How do I set up the graph like this?

Ok, I believe this should work using 3 calculated fields:

  1. Month : extract(‘MM’,{date})
    This field will give you the number of the month (1=January and 12=December)

  2. Year : extract(‘YYYY’,{date})

  3. Cumulative value : runningSum(sum({value}),
    [{month} ASC],
    [{year}])
    Here you’re calculating cumulative sum of ‘value’ over each month but only for that year using partitioning.

You can then use a Line Chart to get your visual:

Unfortunately you won’t have the names of the months but the numbers, but let me know if this helps!

1 Like