Hi,

I have a dataset consisting of total weight, total recycling weight, and rate of recycling.

I am trying to create a calculated field, where I want to show a donut chart showing the total weight being split by total recycling weight and non-recycling weight.

I have 3 conditions which I am trying to satisfy:

1.) If the total weight = total recycling weight (i.e., rate of recycling is 100%) then show the total weight

2.) if the total weight <> total recycling weight (i.e., rate of recycling is > 0% and < 100%) then show the total recycling weight **BUT** within this loop only I want it to the subtracted weight as “Non-Recycling Weight”. (i.e., if rate of recycling is > 0% and < 100%, example: rate of recycling is 25% and total weight is 100kgs then total recycling weight = 25kgs, and I also want it to output 100-25 = 75 kgs as “Non Recycling Weight”. So that I can create my donut chart.

Thank you,

Akshay

Hi @akshaym ,

If I understand the requirement correctly, you have rate of recycling in percentage and total weight as absolute value and you want to put up a distribution chart in absolute values for recycling weight and non recycling weight.

For this use case we can create a gauge chart and change the arc style to 360. Total weight is the target and calculated recycling weight is the value.

The only challenge is you cannot add data label for the difference value - non recyclable. But it can be added as Tooltip if required.

If this is not what you are looking for , please add more details on what data is available and what you want to achieve.

Thanks,

Prantika

1 Like

Hi @prantika_sinha ,

Thank you for providing a solution to this requirement. I like the idea of using a gauge chart. However, my requirement is to use a donut chart with actual split of recycling and non-recycling.

I have attached a snippet of dataset for your reference. Hope it helps.

The columns include - Total Weight (kg), Total Recycling Weight (kg) (**which is nothing but Total Weight (kg) * Recycling (%)**), and Recycling (%).

If you see row 2 - Total Weight (kg) = 2066.14, Recycling (%) = 0%, so Total Recycling Weight (kg) = 0, which should ideally return me total weight using the ifelse condition – ifelse(Total Weight (kg) <> Total Recycling Weight (kg) AND Recycling (%) = 0%, Total Weight (kg)) — which I would create a new calculated field and name it as ‘Non-Recycling’.

But if you look at row 12 - Total Weight (kg) = 11402.87, Recycling (%) = 50%, so Total Recycling Weight (kg) = 5701.44, which should return the 5701.44 as ‘Recycling’ and in the same condition the remaining that is 11402.87 - 5701.44 = 5701.44 as ‘Non-Recycling’

Thank you

Hi @akshaym ,

Will you be able to publish this sample data in arena and share what you have implemented till now?

Thanks,

Prantika

1 Like

Hi @prantika_sinha ,

Thank you for your continued support. I went ahead and create a sample analyses in Arena

Recycling vs Non-Recycling

Additionally I also created a snippet of what I exactly I need and having trouble computing. The ones in green I was able to compute and the one in red is something I am facing challenge computing.

Hope it helps.

Thank you,

Akshay

Hi @akshaym ,

I understand your intent to break down a record which has non zero recycling % to 2 products Recycled and non-recycled. Since the condition 2 and 3 in if clause is same, the first one satisfies and the remaining weight is not accounted in non-recyclable weight.

There is no way to fan out the data at recycled/non-recycled level. All calculation here follow the same rule of data manipulation language, record level calculations.

You can do this in the back end while ingesting data, to create a field and calculate non-recycled weight and then unpivot the columns. However, we cannot do that after ingesting data in QuickSight as of date.

If you want to proceed with the existing format of data, then as mentioned earlier gauge chart is the only option I see.

Sample solution 36320

Thanks,

Prantika

1 Like