cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
nick_88 Frequent Visitor
Frequent Visitor

Count across 5 columns for same values

Hi

 

my excel data has 5 columns that contain 3 common values. (Surgery/Seles/Leding team) or are empty

 

Capture2.PNG

 

My Graph set up:

  • Axis - Hospital spec 1
  • Value - count of name

-say (Surgery/Seles/Leding team) is respectively D, I, L

data.PNG

 

 

Now how can I add remaining 4 columns to the graph to get total across all 5 surgeries ? It does not work by default

Beside there are nulls that i want to filter out. That is easy for 1 column but not sure how it would be when there is 5.

Excluding rows from one column that are null also deleate filled ones from the other . Look at the excel data above.

 

I tried play with query editor:

- pivot  reduced number of categories (Surgery/Seles/Leding team) to just 1 - not sure why

- Transpose - gives me thousand name columns

 

I was thinking of getting total count for each column per category. (would need not count nulls) but how I get it back to graph above?

 

Any idea ?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Count across 5 columns for same values

@nick_88 

 

Hi, In Query Editor try this:

 

-Select the columns with Hospital Spec (1 to 5)

-Transform - Unpivot Columns

-Filter No Blanks Columns in This New Column

 

Regards

 

Victor




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




2 REPLIES 2
Super User
Super User

Re: Count across 5 columns for same values

@nick_88 

 

Hi, In Query Editor try this:

 

-Select the columns with Hospital Spec (1 to 5)

-Transform - Unpivot Columns

-Filter No Blanks Columns in This New Column

 

Regards

 

Victor




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




nick_88 Frequent Visitor
Frequent Visitor

Re: Count across 5 columns for same values

Magic Smiley Happy Works like a charm 

 

Thank you so much