I’m trying to display a running sum visual by month, using a line chart, for analyzing sales growth by fiscal month. This is the formula I use -
runningSum(sum({SALES_AMOUNT}),[{Fiscal Month_No.} ASC],[{fiscal_year}])
I don’t want to use the field from the calculation ‘Fiscal Month_No.’ as it’s not as intuitive for users to comprehend.
Someone suggested appending month# and Month text for both - sorting in calculation+displaying (for eg: 11-Jan,01-Mar)…but I just wish to display text without any digits, so this won’t work
Kindly advise!
Okay, I understand the requirement you’re trying to address. Here’s a suggested approach to display the running sum visual by fiscal month using a line chart, without the need to display the numeric fiscal month value:
Create a Calculated Field for Fiscal Month Text:
In your QuickSight dataset, create a new calculated field that converts the fiscal month number into a text representation.
For example, you can use the following formula:
CASE
WHEN {Fiscal Month_No.} = 1 THEN 'January'
WHEN {Fiscal Month_No.} = 2 THEN 'February'
WHEN {Fiscal Month_No.} = 3 THEN 'March'
...
WHEN {Fiscal Month_No.} = 12 THEN 'December'
END
This will give you a new field called ‘Fiscal Month Text’ (or whatever you’d like to name it) that displays the month name instead of the numeric value.
Use the Fiscal Month Text in the Visual:
In your line chart visual, use the ‘Fiscal Month Text’ field as the x-axis (category) field.
For the y-axis (value) field, use the running sum calculation you provided:
runningSum(sum({SALES_AMOUNT}),[{fiscal_year}])
This will display the running sum of sales amount by fiscal month, using the month name on the x-axis.
Sort the Visual:
To ensure the months are displayed in the correct order (January to December), you can either:
a. Sort the ‘Fiscal Month Text’ field in the visual settings, or
b. Add a sort order custom field to your dataset that maps the month names to a numeric value (e.g., January = 1, February = 2, etc.), and use that field for sorting.
This approach should allow you to display the running sum visual by fiscal month using the month name, which is more intuitive for your users, while still maintaining the desired sorting order.
Let me know if you have any other questions or if you need further assistance!
I noticed in your running sum formula you have only added fiscal_year field, also you haven’t added a sorting field imperative for runningSum function. Doing the above takes the running total by Alphabetical order of Month_text, which isn’t accurate .
My formula: runningSum(sum({sales_amt}),[{Fiscal Month #} ASC],[{fiscal_year}])
For the line chart, each of the 3 fields from the formula have to be added else it fails
This is what I’m looking for (just need to replace X axis with Month Name) -
You’re absolutely right, my previous suggestion was missing a key component for the accurate calculation of the running sum by fiscal month. Let me provide a revised solution that addresses the sorting and includes the fiscal year:
Create Calculated Fields:
Fiscal Month Text (as before):
CASE
WHEN {Fiscal Month_No.} = 1 THEN 'January'
WHEN {Fiscal Month_No.} = 2 THEN 'February'
...
END
Fiscal Month Sort Order:
CASE
WHEN {Fiscal Month_No.} = 1 THEN 1
WHEN {Fiscal Month_No.} = 2 THEN 2
...
WHEN {Fiscal Month_No.} = 12 THEN 12
END
Update the Running Sum Calculation:
Use the Running Sum function with the appropriate sorting and grouping fields:
The runningSum function now includes two sorting fields: [{Fiscal Month Sort Order}, {fiscal_year}]. This ensures that the running sum is calculated in the correct order, first by the fiscal month number and then by the fiscal year.
The {Fiscal Month Sort Order} field is used to sort the months in the correct numerical order (1 for January, 2 for February, etc.), while the {fiscal_year} field ensures that the running sum is calculated within each fiscal year.
Use the Calculated Fields in the Visual:
In your line chart visual, use the Fiscal Month Text field for the x-axis (category) and the running sum calculation for the y-axis (value).
This updated approach should provide you with an accurate running sum visual, where the months are displayed in the correct numerical order, and the running sum is calculated based on the fiscal year.
Let me know if you have any further questions or if you need additional assistance!
“In your line chart visual, use the Fiscal Month Text field…”- This is not allowed as I mentioned in the error message…
*Only fields used in the calculation can be used in the chart
Hi @Ops_Expert,
It’s been awhile since we last heard from you. Were you able to find a work around for your solution or did you have any additional questions?
If we do not hear back within the next 3 business days, I’ll go ahead and close this topic.
Hi @Ops_Expert,
Since we have not heard back from you, I’ll go ahead and close this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for relevant information.
