Is it possible to Count text in columns and t...

(George J) #1

Is it possible to Count text in columns and the results in a COL Series Chart?

Example: Dept

Agree

Disagree

inProgress Finance

Yes Accounting

Yes Finance

Yes Sales

Yes Sales

Yes Accounting

Yes Accounting

Yes Accounting

Yes

So for my COL Series I would like to count each column and give the results for the department.

Finance will have 2 stacks displaying 1 for Agree and 1 for Disagree.

Accounting will have 3 stacks displaying 2 for Agree, 1 for Disagree, and 1 for inProgress.

Sales will have 1 stack displaying 2 for Agree.

(Bellave Jayaram) #2

Make 3 VCs ,one each for the three columns and put formulas like IF([Agree],1,0) etc. for each. Then create a column series chart with the VCs as Chart columns.

(George J) #3

Hi, I did that but it does not count the column totals.

It just shows 1s, and 0s.

So I thought of doing Count(List(If([Agree]=“Yes”,1,0))) etc.

That didn’t work either.

(Bellave Jayaram) #4

Ah yes, sorry it won’t give you the sum; so use COUNT(SELECT (Table [Agree], AND([Agree], [Dept]=[_THISROW].[Dept]))) etc.

(George J) #5

COUNT(SELECT (Table [Agree], AND([Agree]=“Yes”, [Dept]=[_THISROW].[Dept])))

Thank you! This works perfectly!