Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
=COUNT(OrderItemId) / COUNT({<ManufacturerName>}TOTAL OrderItemId)
or
=COUNT(OrderItemId) / COUNT({<1>}TOTAL OrderItemId)
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)
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.
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)
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.
Just to check, what does
=COUNT(OrderItemId) / COUNT( {1} TOTAL < [PaidDate.Calendar.YearMonth] > OrderItemId)
return?
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?
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.
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 ?