Combining data into one pie chart

louiswatson

I'm looking to combine data from 6 sources into one pie chart. All data will be coming from Pipedrive but each dataset uses different filters.

I have setup an ETL which goes through each filter, I'm stuck on working out how to combine the data into two columns (stage and number of panels). Is there an optimal way to combine data into one pie chart?

Cheers

RobSomers

@louiswatson You can use the Add Constants tile in ETL. You'll add one tile per branch of your ETL and name the column the same for each one (Stage), and then for each constant, put the name of the stage that corresponds to the filters.

Additionally, depending on your comfort level with CASE WHEN statements and the complexity of your filters and other tiles, you could do this without having to create different branches using a formula tile with a long case when formula.

MichelleH

@louiswatson How are your 6 data sources related? Without seeing your data and what you are trying to measure, you will likely need to use the Append Data tile in MagicETL.

louiswatson

They are all in a status from Pipedrive called 'Lost', I'd like to show a breakdown of the sites in lost. This is where the additional filters/date ranges differ from each dataset.

All I need to do now is group the values into the filter groups below (tenure, instructed, no tenure, tenure no planning etc) and use the values for the pie chart. I wasn't sure how to group the data and combine together, the datasets are using 'No of panels' as the value. Hopefully this helps?

mhouston

@louiswatson are your stages mutually exclusive? if so, I think your next step will be a group by tile for each stage (group by stage and then count panels?) and then an append which will stack your 5 categories together.

So i'd think your end dataset goal is:

which you'd then build your pie chart off... is this what you're aiming for?

louiswatson

This is what I'm aiming for! The stages are exclusive but they aren't named in Pipedrive, I wasn't sure how to create a stage name from filters in an ETL.

RobSomers

@louiswatson You can use the Add Constants tile in ETL. You'll add one tile per branch of your ETL and name the column the same for each one (Stage), and then for each constant, put the name of the stage that corresponds to the filters.

Additionally, depending on your comfort level with CASE WHEN statements and the complexity of your filters and other tiles, you could do this without having to create different branches using a formula tile with a long case when formula.

louiswatson

Thankyou all, really helpful! The 'add constants' tile in ETL did the trick.

jaeW_at_Onyx

@louiswatson you could have significantly simplified your ETL by using a FORMULA tile with a CASE statement.

The reason why this approach is cleaner is the CASE statement allows you to use an ELSE clause. When you split out your data using FILTER tiles as you have, if you miss a new filter value you exclude it from your output. Which is usually an accident.

Also, by using a CASE statement the logic for all your new labels is gathered into one place instead of spread across several tiles.