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:
Month : extract(‘MM’,{date})
This field will give you the number of the month (1=January and 12=December)
Year : extract(‘YYYY’,{date})
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!
