I have graph that displays data monthly. Problem is when there are no hits to certain month from certain item, the cumulative amount wont appear in graph. I have dynamic levels which user can alter in order to change perspective on dashboard. I think that makes grouping the observed class or item grouping difficult or practically impossible. Is there a workaround for this? Also if there is line showing earlier amounts, graph shows results for those months in this year, even if they are in future. Ss below
1 Like
Hello @reportter, welcome to the QuickSight community!
There may be a few issues here causing your data to not return as expected. When you say the cumulative amount, what calculation are you utilizing to show the cumulative total across the year?
Also, how are you grouping the color by field to split the cumulative amount by month? Depending on your data, it would be really beneficial in the debugging process, if you are able to create a demo version of this visual in QuickSight Arena where I could test possible solutions directly in an analysis. Here is a blog with some information on Arena:
With some more information, I can help guide you further. Thank you!
1 Like
Hey, thanks for fast respond, if you can see whats the problem from these details:
STACKED BAR COMBO CHART:
X-axis:
extracted_month (to number 1-12)
bars:
runningSum (
sum(YEARLY AMOUNT),
[{extracted_month} ASC],
[{LEVEL}]
)
group/color for bars:
LEVEL
line in graph:
runningSum (
sum(YEARLY AMOUNT (last year)),
[{extracted_month} ASC],
[{LEVEL}]
)
Parameter LevelParameter, which controls the shown level in graph
LEVEL -calculated field
ifelse(
${LevelParameter} = “employee” AND isNotNull(employee),employee,
${LevelParameter} = “item_group” AND isNotNull(item_group),item_group,
${LevelParameter} = “item” AND isNotNull(item),item,
${LevelParameter} = “employee_shop” AND isNotNull(employee_shop),employee_shop,
“Level not selected”
)
If it is still hard to see the problem, Ill try to make the graph in Arena. Is there any ready datasets or do I need to make one there?
Hello @reportter, my initial thought is instead of just using sum(YEARLY AMOUNT) within the bar calculation, you should create an initial calculated field that will only return data for the current year. Do you have a date field in your dataset besides the extracted month? Are you creating the x-axis field with this calculation on a datetime field:
If so, we can use that original date with truncDate or extract to filter for the current year:
This Year = ifelse(truncDate('YYYY', {date field}) = truncDate('YYYY', now()), {YEARLY AMOUNT}, NULL)
Now, you can use that calculated field in the bars calculation:
runningSum (
sum(This Year),
[{extracted_month} ASC],
[{LEVEL}]
)
If that doesn’t resolve the issue, I believe the problem is likely with the LEVEL calculation. That will be a lot easier to test from QuickSight Arena. If you can utilize a demo/anonymized version of your data from an excel file, that would be the easiest way to debug. Otherwise, I can create an Arena analysis with demo data that will be completely different.
It also may be easier to test if you piece these calculated fields together one by one. With the suggested calculated fields I wrote above, are you able to create a regular stacked bar chart without a color by field? Is it returning the runningSum values that you are expecting?
With some more info, I can help guide you further. Thank you!
1 Like
Hey,
The calculated field in bars is technically same and if the amount is in table, the runningsum -function gives correct amount. The problem I think is missing values that graph doesn’t show, for example user is using item_group level, which consists item 1,2,3. Everything looks good when rows are observed on that level, but when user uses item level and sees item 3 which have sales only in january and september the graph shows amounts in january and september and others are empty, because there are no data for other months. Cumulative sum is correct, but it is missing from majority of months due to lack of data points. Is only way to do very complex calculated field that calculates manually sums for each month and adds them to last month sum. Is that even doable? Im pretty sure that supposed to happen with the runningsum -function, but it can’t handle situations that data doesn’t have data input in every month. Showing identical sum from current month to rest of the year when there are data from earlier years on same month seems bug for me.
Does this explain my problem better?
I made This Year -calculated field and cumulative bar chart with the formula given and it gave exactly the same results, the missing value handling seems to be the problem.
If you can create Arena with demo data, I’d happily show the problem there.
Thanks for your answers so far!
2 Likes
Hello @reportter, I have another thought that may resolve this. What if you remove the partition for the LEVEL field within the runningSum calculation? I am curious if the visual will still color the bars properly if you remove the partition, then the cumulative sum shouldn’t have any breaks in the data.
We could try leaving the partition field blank or just partition by year and see if that works.
Blank Partition:
runningSum (
sum(This Year),
[{extracted_month} ASC],
[]
)
Partition by Year:
Year Date = truncDate('YYYY', {date field})
runningSum (
sum(This Year),
[{extracted_month} ASC],
[{Year Date}]
)
The partition is splitting the data based on the level so it is causing some months to show blanks. Utilizing the LEVEL field to group by/color the bar chart without partitioning the cumulative sum will hopefully resolve the issue.
1 Like
Hi,
After removing partition in function gives correct results in graph when level is not selected. After selection earlier months sum to all items on level evenly and added the amount they gain on each month. So it doesn’t seem to work.
It seems that there is no way around this issue with switchable level on graph
I tried those formulas also you gave, but that with runningsum partitioned by Year Date didn’t work due to missing reference.
What can I try next?
1 Like
Hello @reportter, honestly, if neither of these options work, I think it is likely going to require a work-around solution rather than an exact fix. I think utilizing the runningSum by an extracted date value and partitioning dynamically with a calculated field is going to continue to hit limitations. If we set this to show the runningSum, month by month, for a single year rather than contain data for 2 years, we may be able to find an alternate solution.
I built some options in an Arena analysis Cumulative bar chart is not displaying all data when grouped and with dynamic data levels
What I did find, is a possible solution to the first issue you were facing. In your calculated field for Level, you are excluding scenarios where the value is NULL. I think instead, you should include the NULL values. That will allow the runningSum to continue to aggregate the grouping for the year and seems to resolve the issue of excluding the group across those months. In the 2nd bar chart I created, I excluded a few months of data for Finance and it shows the running total across those months. To see the Finance option, select industry in the control dropdown.
I think this will be the closest we are able to get towards your desired solution. Thank you!
1 Like
Hi,
Thanks for your effort for that Arena dashboard. The numbers are correct, but in graphs for example in february is missing product Alchemy, which has value in january and march. I’d say it should have included february also. With extracted month it seems that there is no need for data from earlier years in order to graph show bars with no data to future months. This second problem can be seen best with customer level. But these seem to be limitations so, it is what it is.
Thank you so much for you help and effort @DylanM !
1 Like