Ignore selections in one field in set analysis

[Ashwinyp]

Hi,

I am having 3 filters in my sheet - Geography, industry type and Quarter-Year.

I am trying to create a Measure in a Table which counts the number of applications only in Q1 2019. I do not want this measure to get affected by selections made in "Quarter-Year" filter. However, I need it to get filtered by Geography and industry type (Geography is created as a drilldown dimension).

I have tried Count( { <qtr_year = {'Q1_2019'} > } app) but this gets affected when anything else is selected in the "Quarter-Year" filter. Please help!

[mahaveerbiraj]

HI Ashwini ,

U need to nullify that column in the set expression , like if you want to ignore   "Quarter-Year" selection use set Analysis like below 

sum(  {< [Quarter-Year]=  > }   sales) 

you just need to pass that column in the set analysis(expression) like above

let me know if you are not clear 

[Ashwinyp]

I need this column to show me the count of applications for "Q1_2019" alone. So if any other quarter like Q2_2019, etc is selected from the filter, it should still show me the count of applications for Q1_2019. This does not work with the expression you have provided me. Do you have an alternate solution?

[sunny_talwar]

What is a difference between Quarter-Year and qtr_year field? Are they two different fields in your data model or one of them is a typo? If they are two fields... then may be this

Count({<qtr_year = {'Q1_2019'}, [Quarter-Year]>} app)

[mahaveerbiraj]

Then try like below ,

Count( { < [Quarter-Year],qtr_year = {'Q1_2019'} > } app)

[Ashwinyp]

Sorry about that, it was a typo. Both are the same field.

I would like the measure to show me the count of applications for Q1_2019 irrespective of the selection in the "qtr_year"  filter. I do not want it to ignore selections made in the Geography and industry filters though.

[sunny_talwar · Accepted solution]

In that case, I don't see why this isn't working

Count({<qtr_year = {'Q1_2019'}>} app)

This will ignore any selection in qtr_year, but still allowing you to select other fields... is this not what you are seeing? 

[mahaveerbiraj]

try below  sure it will help you,

Count( { <qtr_year ,qtr_year = {'Q1_2019'} > } app)

[sunny_talwar]

@mahaveerbiraj AFAIK fields can only be added to set analysis once. Have you tested this?

[Ashwinyp]

Thanks everyone for the solution.

I just figured out why it was not working. I had an "If" condition on the 'qtr_year' filter. One of the Quarters was called "Q_" and in order to ignore it, my filter had a condition as if(qtr_year <> 'Q_', qtr_year)

Due to this, my measure was not working. But if I use qtr_year as it is in the filter, it works. I don't understand why this is an issue though.