Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

marcoyukon
Contributor

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
MVP
MVP

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

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

or

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

MVP
MVP

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

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
Contributor

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

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
Contributor

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

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
Contributor

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

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.

MVP
MVP

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

Just to check, what does

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

return?

Highlighted
MVP
MVP

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

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
Contributor

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

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
Contributor

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

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 ?

Community Browser