Skip to main content
Announcements
Document boards are being consolidated, this board no longer allows NEW documents READ MORE

Ignore all selections except some specific fields using Set Analysis

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Ignore all selections except some specific fields using Set Analysis

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).

Labels (1)
Comments
johndoub
Contributor III
Contributor III

i have 2 date fields, sales_dt and receivings_dt.  i have set each up in a separate date picker filter.

the sales_dt field is used to sum(sales) and the receivings_dt field is used to sum(received-cost).

if I select  only one date range, i.e. for sales_dt, then it gives the correct sum of sales.   but if select both date ranges, then both sum(sales) and sum(received-cost) are incorrect. 

how do i write an expression that ignores the date range selected for the other measure?

 

sshastry
Contributor II
Contributor II
 

This is brilliant.  I was working on similar issue for the past 3 days .. this totally saved me. Thank you 

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

 
0 Likes
Version history
Last update:
‎2011-01-26 06:06 AM
Updated by: