I'm creating a simple list with three fields - Customer, Gender, and Count (measure).
It currently looks like this:
Company Gender Count
Company A | M | 20
Company A | F | 30
I'd like to modify it to show: Customer, Male Count, Female Count... however I can't figure out how to filter my measure field accordingly. I've tried creating these fields from scratch as query expressions, however I can't make it work.
Final result should look like:
Company Male Count Female Count
Company A 20 30
Try adding 2 Columns, such as ..
"Male Count"
"Female Count"
You want to populate each row value in those 2 Columns as 1 or 0.
A Data Item for the Columns "Male Count" would have an expression like ...
Case
When [Gender] = 'M' Then 1
Else
0
End
A Data Item for the Columns "Fenale Count" would have an expression like ...
Case
When [Gender] = 'F' Then 1
Else
0
End
You can Total those Values to get the Male and Female Counts.
HTH, Bob
Quote from: Cognos_Jan2017 on 09 Mar 2018 01:10:48 PM
Try adding 2 Columns, such as ..
"Male Count"
"Female Count"
You want to populate each row value in those 2 Columns as 1 or 0.
A Data Item for the Columns "Male Count" would have an expression like ...
Case
When [Gender] = 'M' Then 1
Else
0
End
A Data Item for the Columns "Fenale Count" would have an expression like ...
Case
When [Gender] = 'F' Then 1
Else
0
End
You can Total those Values to get the Male and Female Counts.
HTH, Bob
Thanks I'll give it a shot. On second thought though- perhaps you'd know how to just change the visualization settings to display percentage? Using the example numbers I provided, when I change the format to percentage it obviously just changes the 20 and 30 to 20% and 30%, rather than calculating their actual percentage. Is there a simple way to get it to display the percentages as I described?
When using a List for something like this, I "Sigma" for Total, and take note of how Cognos writes those Totals for, in this case, the Male Count and Female Count Columns.
Then I write 2 Query Calculations where the denominator for both Query Calculations adds the Male and Female Totals.
One Query Calc is [Male Count]/([Male Count]+[Female Count]), while the other Query Calc is [Female Count]/([Male Count]+[Female Count]).
I then keep the Company Column visible cutting the Columns for [Male Count] and [Female Count]. Cutting those Columns retains them in the Query. I unlock the Totals Row (can do for both Header and Footer) and add the names of the Query Calcs for Male and Female.
The above may sound confusing but try that until you see how it works.
As for Visualizations (which I am learning now), IE, the Legacy Pie Chart can display both Absolutes and Percentages. I have a current thread where I am able to display EITHER Absolutes (Values) OR Percentages. I am testing Pies, Stacked Column, and Pareto Charts, and will add more to those Topics next week.
HTH, Bob
Quote from: pacificbeavs on 09 Mar 2018 12:56:13 PM
I'm creating a simple list with three fields - Customer, Gender, and Count (measure).
It currently looks like this:
Company Gender Count
Company A | M | 20
Company A | F | 30
I'd like to modify it to show: Customer, Male Count, Female Count... however I can't figure out how to filter my measure field accordingly. I've tried creating these fields from scratch as query expressions, however I can't make it work.
Final result should look like:
Company Male Count Female Count
Company A 20 30
Count(case [Country]
when 'Belgium'
then
case [Product line]
when 'Camping Equipment'
then 1
else
0
end
else
0
end)
Use Company for Country, Company A for Belgium, Gender for Product line and 'M' for Camping Equipment
Use Company for Country, Company A for Belgium, Gender for Product line and 'F' for Camping Equipment
Regards,
Pratap
Quote from: pacificbeavs on 09 Mar 2018 04:27:01 PM
Thanks I'll give it a shot. On second thought though- perhaps you'd know how to just change the visualization settings to display percentage? Using the example numbers I provided, when I change the format to percentage it obviously just changes the 20 and 30 to 20% and 30%, rather than calculating their actual percentage. Is there a simple way to get it to display the percentages as I described?
Count of Male:
Calculation field 1 :
Count of Male :case [Country]
when 'Belgium'
then
case [Product line]
when 'Camping Equipment'
then 1
else
0
end
else
0
end
Use Company for Country, Company A for Belgium, Gender for Product line and 'M' for Camping Equipment
Use Company for Country, Company A for Belgium, Gender for Product line and 'F' for Camping Equipment
Calculation Field 2
Over all Count :Count([Country])
Use Company for Country
Calculation Field 3:
Over all Count in Percentage :([Count of Male]/[Over all Count])
Then go to Report Page explorer and Click on Over all count in percentage Column (List Column Body) and go to Properties pane and Data format -->Percentage symbol - %,Number of decimals - 2 and Decimal seperator - '.'
The same we have to create for Female in another column
Regards,
Pratap