Ignore all selections except some specific fields using Set Analysis

[#1]

Hello.

As far as I was designing apps for QV - I've found a great need in some charts to ignore all possible selections except some specific fields.

Exploring possible ways to do so - the first my decision was to use dollar-sign expression inside my Set Analysis which could make an exception like next:
(Let's imagine that in my scheme there are fields [Stock], [Manager], [Date], [City], [Client] and [Amount])

sum({1<[Stock]={'Stock 1','Stock 3'}>} Amount)

If I selected only 'Stock 1' and 'Stock 3' in field [Stock] - and this way has shown it's profit - you can get it using this expression:

sum({1<[Stock]=$(=if(GetSelectedCount([Stock], true( ))>0,'{' & chr(39) & GetFieldSelections([Stock],chr(39) & ',' & chr(39),GetSelectedCount([Stock], true( ))) & chr(39) & '}', ''))>} Amount)

If you need two, three, or more fields - you can simply add such code for every field into Set Analysis:

sum({1<[Stock]=$(=if(GetSelectedCount([Stock], true( ))>0,'{' & chr(39) & GetFieldSelections([Stock],chr(39) & ',' & chr(39),GetSelectedCount([Stock], true( ))) & chr(39) & '}', '')),[Manager]=$(=if(GetSelectedCount([Manager], true( ))>0,'{' & chr(39) & GetFieldSelections([Manager],chr(39) & ',' & chr(39),GetSelectedCount([Manager], true( ))) & chr(39) & '}', ''))>} Amount)

But what if you have a field containing hundreds of records? Can you imagine what will be the length of your expression? That's why I started to discover another way to do that. I've thought - what if I use not "1" Set, but "$" Set in my expression and write in it all the fields that must be ignored, something like that:

sum({$<[Date]=,[City]=,[Client]=,[Amount]=>} Amount)

So, using such expression - I get that all the fields except for [Stock] and [Manager] are ignored, but how car I make it automatic? Sure using our lovely dollar-sign expressions and system field $Field:

sum({$<[$(=Concat({1<$Field-={'Stock','Manager'}>}distinct $Field,']=,[')&']=')>} Amount)

where inside the set analysis of Concat function you can insert fields that you are interested in (by the way - there you can insert even tables using system field $Table and others).

[#2]

Hello, everyone.

I was wondering if it's possible to use this formula with Min and Max functions?

Basically, I am trying to create a measure in Pivot table that would calculate difference between Average calculated Price ( Sum([Sum])/Sum([Volume]) and minimum price for the specific product in the specific year (I have a separate column with all Materials prices calculated in data table).

In Rows I have: Material, Supplier, Factory.

In Columns I have: Year, Quarter (those are not time measures but separatly calculated columns in in data table).

But, when I am using Min({$<[$(=Concat({1<$Field-={'Material','Year'}>}distinct $Field,']=,[')&']=')>} Price). it acts as if I am using simple formula Min(Price) - which takes for calculation minimum price from the specific Quarter, but not from the specific year.

Does anyone know how to find minimum price in a specific year that is selected in the Pivot table, but ignores other time selections, such as Quarters?

Respectfully

Lev

[retko1985]

This seems to work for me:

For you example:

=sum({1<Stock =$:: Stock, Manager =$:: Manager>}Sales)

[ysj]

Excellent

[blunckc1]

Hi all,

Hoping to get some help with this.  I currently use the following set analysis expression to calculate the total number of hours an employee has been rostered for:

Sum({$<RosteredFlag={'1'}>}Hours)

However I need to modify this so the results aren't impacted when a user filters on a dimension called [Time Category D] which contains a particular leave type.  At the moment, when the [Time Category D] dimension is selected by a user the total number of hours an employee has been rostered for matches the total number of hours an employee has been rostered in a [Time Category D] leave type.  Which it shouldn't.  It should stay at the total number of hours they were rostered for.

Can't change the set analysis from '$' to '1' because I do need the calculation to update when a whole range of other dimensions are selected.

Just need it to ignore selections made in [Time Category D].

How is this achieved?

[caio_caminoski]

Hi Carl,

Try

Sum({$<RosteredFlag={'1'}, [Time Category D] = >}Hours)

Regards,

Caio

[blunckc1]

Hey Caio,

Thank you! Now that I see the solution I feel a bit silly having not seen it the first time. Appreciate the assist.

Cheers

Carl

Carl Blunck

Principal Organisational Effectiveness Specialist

(People Analytics & Systems)

People and Culture

Children’s Health Queensland Hospital and Health Service

Level 9 | Centre for Children’s Health Research

Graham Street, South Brisbane QLD 4101

T: 07 3069 7052

E: carl.blunck@health.qld.gov.au<mailto:carl.blunck@health.qld.gov.au>

W: www.childrens.health.qld.gov.au<http://www.childrens.health.qld.gov.au/>

<https://www.facebook.com/childrenshealthqld>

<https://twitter.com/childhealthqld>

<http://www.linkedin.com/company/children's-health-queensland-hospital-and-health-service/>

<https://www.instagram.com/ladycilentochildrenshospital/>

Children's Health Queensland acknowledges the Traditional Custodians of the land,

and pays respect to Elders past, present and future.

[cheenu_janakira]

Hi Denys,

Thank you for sharing. This is super smart. I extended this a little with Wildmatch which can work well with a properly maintained data model and/or naming convention.

I have an app where I wanted to exclude all fields except those with field name containing "Davis Cup" and the field "Year", this is what I did using your technique:

ONLY({$<[Cup Year] = {$(=MAX(Year))}, $(='[' & CONCAT(DISTINCT IF(AGGR(WildMatch($Field, '*davis*cup*', 'Year'), $Field) = 0, $Field), ']=,[') & ']=')>} [Cup Winner])

All you need to do is include in the wildmatch strings, the fields on which you still want the set analysis to react upon selections (the exceptions list). This allows for a less than 100% fields being excluded in the set analysis.

Thanks for sharing.

Cheerio,

Cheenu

[Anonymous]

Hi everyone, please tell me someone has used this formula in QlikView 12? It's exactly what I need to use for a use case I am working on but I just can't get the syntax right. Please assist! Thank you.

[SevillaFe]

Hello, I need your help with $Table and $Field. I have a Table1 and Table 2. They are joined and I need that my selections ignore Table1 except some fields. I´m trying that: count({<$(=concat( {1<$Table={'Table1'} ,$Field-={'datum','[nummer1-nummer2]','Cluster','country','repair'}>} '[' & $Field & ']=',','))>} distinct([nummer1-nummer2])) but when I selected "country" or "[nummer1-nummer2] i.e. it doesn´t work and I cannot find the problem Thank you

[devvratparashar]

it helps a lot