Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
marcoyukon
Creator
Creator

How to have COUNT (TOTAL..) to ignore one selection.

I have the following measure expression:

COUNT(OrderItemId) / COUNT(TOTAL < [PaidDate.Calendar.YearMonth] > OrderItemId)

But wanted for the total on the denominator (above) not to change when the dimension [ManufacturerName] is selected.

What can I include below to ignore the [ManufacturerName] selection:

COUNT(TOTAL < [PaidDate.Calendar.YearMonth] > OrderItemId)

10 Replies
MK_QSL
MVP
MVP

=COUNT(OrderItemId) / COUNT({<ManufacturerName>}TOTAL OrderItemId)

or

=COUNT(OrderItemId) / COUNT({<1>}TOTAL OrderItemId)

swuehl
MVP
MVP

Add a set expression to ignore selections (either specific or all):


=COUNT(OrderItemId) / COUNT({<ManufacturerName=>} TOTAL <[PaidDate.Calendar.YearMonth]> OrderItemId)


or


=COUNT(OrderItemId) / COUNT({<1>} TOTAL<[PaidDate.Calendar.YearMonth]> OrderItemId)


The Aggregation Scope

marcoyukon
Creator
Creator
Author

Thank you, but it still doesn't work for what I need.

I tried the following:

1) COUNT(OrderItemId) / COUNT( { <ManufacturerName> }  TOTAL < [PaidDate.Calendar.YearMonth] > OrderItemId)

2) COUNT(OrderItemId) / COUNT(  { <ManufacturerName=> }  TOTAL < [PaidDate.Calendar.YearMonth] > OrderItemId)

But the totals still change if I filter on ManufacturerName.

3) I didn't try with ...{1}... since I need any other dimension filters to apply.

Note there is a TOTAL modifier(< [PaidDate.Calendar.YearMonth] > ) on the original expression.

Regards.

marcoyukon
Creator
Creator
Author

Thank you. I tried, But not sure what I am doing wrong. My totals change if (for instance) I select a single ManufacturerName. In this case I get a 1.0 result, like it is not ignoring the selection.

=COUNT(OrderItemId) / COUNT( { <ManufacturerName=> } TOTAL < [PaidDate.Calendar.YearMonth] > OrderItemId)

marcoyukon
Creator
Creator
Author

Not sure if this has anything to do with it, but I am using a PIVOT table. [ManufacterName] on columns, [PaidDate.Calendar.YearMonth] on rows and [Mfg %] expression above as the only measure.

swuehl
MVP
MVP

Just to check, what does

=COUNT(OrderItemId) / COUNT( {1} TOTAL < [PaidDate.Calendar.YearMonth] > OrderItemId)

return?

swuehl
MVP
MVP

Could also be an issue with derived fields and set analysis:

Derived fields not working in Set Analysis

Could you try using an explicitely created calendar?

marcoyukon
Creator
Creator
Author

Replacing:

a) =COUNT(OrderItemId) / COUNT( { <ManufacturerName=> } TOTAL < [PaidDate.Calendar.YearMonth] > OrderItemId)

with:

b) =COUNT(OrderItemId) / COUNT( {1} TOTAL < [PaidDate.Calendar.YearMonth] > OrderItemId)

and .. it works! Thanks!

But.. I am not sure why when I explicitly say to ignore (a -  above) it does not apply.

Furthermore.. I thought {1} would be to ignore ALL selections. I have other dimensions that can be selected, and they should NOT be ignored when selected. I will check the results when other selections are applied to see if those are ignore or not.

marcoyukon
Creator
Creator
Author

Thanks for the article. Yes, we are using the Qlik auto generated calendar. Is it best practice to instead use a calendar Dim external to Qlik in a warehouse database ( if available )  for performance and perhaps more reliable for scenarios like these ?