How do you filter a measure in a query calculation?

[pacificbeavs]

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

[Cognos_Jan2017]

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

[pacificbeavs]

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?

[Cognos_Jan2017]

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

[Pratap Reddy]

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

[Pratap Reddy]

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