Displaying cumulative total in donut chart

Question & Answer
, ,
1

Hi,

I have a requirement where I need to show the number of sales made each year, and in the same graph show the cumulative total. Which I was able to recreate. Next I want to show the cumulative totals in a donut chart which is a drill down from the cumulative total graph showing the split across multiple regions, where it shows what is the total sales made up to that year including the running sum. However, when I click on the donut chart, it only shows me the sales made for that year by that region not the cumulative total.

Any idea how I can go about creating this requirement, so that I can display the cumulative total in the donut chart.

image
image1187×429 17.1 KB

Also, my data is joined, due to which I have to take distinct_count of the measure

2

Hello @akshaym !

Can you breakdown how the date field for the columns of your pivot table work i.e. are you truncating a date field to the year? Also, how have you set up the drill down for the pivot table to the donut chart?

For example, I was able to recreate this without drill downs from the way I set up my fields and aggregates on the field wells:

image
image589×630 37.2 KB

3

Hi @duncan,

Thank you for your response.

To answer your questions
1.) My date field is already in year format
2.) The pivot table is just a visual representation, I am using a bar graph to do the drill down, where in the bar graph I have 3 measures - number of sales each year, then a cumulative total and a lag of cumulative total. My idea of this is that the when I click on the lag of cumulative total - the donut chart should show we split across regions. But right now it only shows me the split for that particular year only, not for all the years in total

Thanks,
Akshay

4

Hi @duncan @DylanM,

I would highly appreciate a response on this issue I have.

I tried to dig in a bit to understand, I saw a few mentioning the use of parameter in the tableau community. But I am not sure how this would work. So my requirement is that I am using a count function to get a total count of sales each year which forms to be my bar chart. In this I have added an action type to do a drill down. Where the second visual which is my pie/donut chart grouped by regions should show me cumulative total till that year i.e. from a year selection in my bar chart, the pie chart should show region by region split of sales till that year.

Thanks,
Akshay

5

Hello @akshaym !

What do you mean by you created a drill down action?

Did you create a custom filter action to go from a selection on your bar chart?

If you have a lag function that is able to show your cumulative sales up to a year, you can use a parameter value that passes a year and filter the donut chart based on the user selection of the bar chart.

6

Hi @duncan,

Thank you for your reply. Apologies if my previous comment(s) created confusion.
Let me try to explain my query in brief.

I have a 3 fields 1.) which shows me number of licenses sold, 2.) a year field and 3.) type of license sold .

My first calculated field is a #Of Licenses Sold Each Year which I have computed using count({# of License}, [{Created Date}]). Which perfectly gives me the count of licenses sold each year, when plotted on a bar chart with this calculated field on the x-axis and the created date on the y-axis.

Now, I created a new calculated field to calculate the RunningSum, which I did using this formula

runningSum(
  sum(count({# of License}, [{Created Date}, {License Type}])), 
  [{License Type} ASC], 
  [${SelectedYear}, {License Type}]
)

The ${SelectedYear}is a parameter just as you mentioned above which I have created and the datatype is integer. The issue is we don’t sell each license type every year. One license type might be sold in 2022, while in 2023 we might have not. The RunningSum calculation ignores the license type when not sold in that year, whereas I wanted it to show me all license types I have sold till that year, when I click from the bar graph using a drill down. But rather it only shows me the count of licenses I sold in that year. I want to use a donut chart with only license type in the group by field and whatever calculated field I need to get the desired result

image
image742×492 23.6 KB

image
image699×483 28.1 KB

image
image1370×499 55.1 KB

7

Hey @akshaym !

Could you try adding this to arena so that I could try a few different potential solutions?

You could also try the following calculated field for your donut chart:

runningSum(sum(ifelse(Created Date} <= ${SelectedYear},{# of License},0)),LicenseType}] ASC)

Let me know if you run into any errors with that calcl.

8

Hi @duncan ,

Thank you for providing a solution. I tried the calculation you provided, but I am still facing issues getting the required result.

Please find attached link to the dashboard in Arena

[License Reporting] (https://community.amazonquicksight.com#Arena-dashboardId=894525b5-29fc-4a5a-bfac-6fe4ff7fa456&authorId=CU-8779)

image
image1582×622 76.6 KB

Let me re-explain the problem so that you have clarity of what exactly I need.

License Reporting
License Reporting1138×585 166 KB

The # of License column shows the user accounts created, due to which I did a distinct count using company name to get the total licenses sold each year which is my 1st calculation


`Calculation 1`

# Licenses Sold Each Year  

distinct_count({Company Name}, [{Created Date}])

My 2nd calculation is the cumulative total which I created using RunningSum

`Calculation 2`

# Cumulative Totals  

runningSum ( sum(distinct_count({# of License}, [{Created Date}, {Company Name}])), [{Created Date} ASC] )

My 3rd calculation is created using the lag function

`Calculation 3` 

lag({Cumulative Totals},[{Created Date} ASC],1)

All these 3 calculations I now show on a stacked combo bar chart.

The next graph I created is a donut chart, which should show me running sum split by license type. Here I used the calculation you provided.

`Calculation 4` 

# Donut Chart     
                             
runningSum(sum(ifelse(extract('YYYY', {Created Date}) <= ${Year}, {# of License}, 0)), [{License Type} ASC])

But, if you see the # of Licenses is actually the count of users account created. License sold is only 1 per company. So as soon as I add a distinct count in this Calculation 4 #. I get wrong result.

Another problem, if you see the data each license type is not sold every year. Eg. In year 2012 - 1 Eternal License and 1 Echo License was sold. In 2015 2 Time-Wrap License were sold, so going back to the stacked combo bar chart, when I click on the year 2015. I want to see in the donut chart 1 Eternal License, 1 Echo License and 2 Time-Wrap License. Showing the total as 4 Licenses in the donut chart totals

And the parameter ${Year} is an integer which passed value as 2024.

Thank you for your help in advance.

Thanks,
Akshay