Count Summary Number

Jeremy
Jeremy Member
in Magic ETL

I'm having an issue adding up multiple columns to present an overall summary in a pie chart.  Below is a sample of the columns.  These are scored given by managers during visits to their sites.  I'm trying to combine the two to create a chart that shows the total number of mastered, in place, not in place, or blank.  I thought doing Count(`Rating 1`) + Count(`Rating 2`) would work but the numbers are off.  I know if I create one for just the first rating using count that it is correct but I need to combine the data (sometimes up to 9 ratings per topic).  

 

Does anyone know how I could go about getting that information?  I'm also struggling with which categerory to put as Pie Name as I'm putting the formula as Pie Value.

 

Thanks for the help and forgive my ignorance in this stuff.

 

 

RatingRating 2
  
  
  
  
  
  
In PlaceIn Place
In PlaceIn Place
In PlaceIn Place
MasteredMastered
MasteredMastered
MasteredMastered
MasteredMastered
MasteredMastered
MasteredMastered
MasteredMastered
Mastered 
MasteredMastered
In PlaceIn Place
MasteredIn Place
In PlaceIn Place
  
MasteredMastered
  
  
Not in PlaceNot in Place
In PlaceIn Place

Best Answer

  • AS
    AS Coach
    Answer ✓

    What would you want your pie chart to look like?  A breakdown of counts of individual scores across every site and rating number? In your case below, that would mean of 54 responses, 19 were Mastered, 13 were In Place, etc.

     

    If that's the case, you might have to rearrange your dataset first.  With the pie chart, the card is evaluating a count of each series value in a single column. You need to get your responses into a single column.  Magic ETL has a wonderful set of transforms called Collaspe/Uncollapse Columns for that.  It takes all of your columns, puts them into one, with an extra column for the rating number.  Your pie chart would be a count of ratings (pie values) per rating type (pie name).

     

    Try to use that and let us know how it goes.

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Answers

  • AS
    AS Coach
    Answer ✓

    What would you want your pie chart to look like?  A breakdown of counts of individual scores across every site and rating number? In your case below, that would mean of 54 responses, 19 were Mastered, 13 were In Place, etc.

     

    If that's the case, you might have to rearrange your dataset first.  With the pie chart, the card is evaluating a count of each series value in a single column. You need to get your responses into a single column.  Magic ETL has a wonderful set of transforms called Collaspe/Uncollapse Columns for that.  It takes all of your columns, puts them into one, with an extra column for the rating number.  Your pie chart would be a count of ratings (pie values) per rating type (pie name).

     

    Try to use that and let us know how it goes.

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Jeremy
    Jeremy Member

    Took a bit (have to love huge files) but this is working.  Thanks for steering me in the right direction.

Categories