Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Lobo77
Contributor III
Contributor III

Filter across multiple dimensions

Hi everyone. Long time reader ,first time poster.

Question. Is there a way to have a ‘yes’ , ‘no’ filter with conditions across multiple dimensions.Please note , I do not have access to loading scripts, creating master dimensions etc.

Hopefully I am using the correct terminology.

I have Orders that have many dimensions. Is there a way to have a calculated dimension/filter to show/flag only the orders with the combinations like highlighted in red below  ( order id 1234)?

I am able to create simple set expressions with if and match etc conditions , but when I try filter across multiple dimensions/fields , I can’t seem to either get the syntax  correct or result.

Result I am after would be like:

Only show order ids  if ([DIM 1] ‘T1’, [DIM 2] ‘WAITING’ , [DIM 3] ‘ALWAYS’) and [(DIM 1]’T2’, [DIM 2] ‘COMPLETE’ , [DIM 3] ‘ALWAYS’) 

Is this possible , or only via script ?

Thanks advance, a lot of the posts have already been invaluable.

 

Order ID

DIM 1

DIM 2

DIM 3

1234

T1

WAITING

ALWAYS

1234

T2

COMPLETE

ALWAYS

2345

T2

NOT DONE

SOMETIMES

3456

T1

WAITING

ALWAYS

3456

T1

COMPLETE

ALWAYS

3456

T2

COMPLETE

SOMETIMES

5678

T33

NOT DONE

ALWAYS

5678

T54

WAITING

SOMETIMES

 

 

21 Replies
gmenoutis
Partner - Creator II
Partner - Creator II

To be honest, I've never tried using a calculated dimension in a filter pane object. I will keep this in mind as a potential impossibility.

Try this: instead of a filter pane, add a Straight Table chart. Add a dimension, and use your formula. Does it now display Yes/No correctly?

Lobo77
Contributor III
Contributor III
Author

Thanks.

Tried  in a straight table , same result.

Exported one order (excel  attached )  that has the has the conditions required to flag a 'yes'. See the expression in column E.

Aggr
(MaxString('')&
if
(
([Task Name]='WW2' and [Original Status]='Waiting' and [ABC_STATUS]='Complete')
or
([Task Name]='WW1' and [Original Status]='Completed' and [ABC_STATUS]='Complete')
,'Yes'
,'No')
,ORDER_ID
)

gmenoutis
Partner - Creator II
Partner - Creator II

Ok, it seems we both missed out on something there - you did not notice my question if Order Id is the maximum granularity, and I did not notice your screenshots well enought.

It makes no sense to use if() when the same Order ID has multiple lines. Instead, we will have to check if there is at least one line that satisfies you criteria.

Try the following, it works in sample app:

=Aggr
(maxstring('') &
if
( count({<[Task Name]={'WW2'},[Original Status]={'Waiting'}>+<[Task Name]={'WW1'},[Original Status]={'Complete'}>}[Task Name])>0
,'Yes'
,'No')
,ORDER_ID
)

Lobo77
Contributor III
Contributor III
Author

Thanks ! 

That works so much better. I misunderstood 'granular'. 

 What it does now, is flag orders that  have  both WW1 or WW2 in either status ( Waiting or Complete ) and combinations there of . So it is functioning  more like 'either/or' , as opposed to 'only'. 

Is this where P() function could work ?

gmenoutis
Partner - Creator II
Partner - Creator II

It is highly unlikely the set analysis modifiers break apart and mix-match.

Can you please specify the requirement as clearly as possible, and then send an example that does not work? (not needed to be many lines, just a few to grasp whats going wrong)

Lobo77
Contributor III
Contributor III
Author

Thanks.

Example ; Order id 37123752 should display No , as ' WW1'  has a status of 'waiting',  when the condition in the formula is Task WW1 should be 'completed'.

Order 47124552 is similar , in this case 'WW2" is in 'Completed' , when the condition is 'Waiting'

Hope this makes sense.

Cheers

 

ORDER_IDTask NameOriginal StatusResultExpected result
37496752WW1CompletedYESYES
37496752WW2WaitingYESYES
37123752WW1WaitingYESShould be NO
37123752WW2WaitingYESShould be NO
47124552WW1CompletedYESShould be NO
47124552WW2CompletedYESShould be NO
Lobo77
Contributor III
Contributor III
Author

Thanks Gmenoutis. Have tried a few diffrent ways now , still no joy. 

gmenoutis
Partner - Creator II
Partner - Creator II

Try this:

=Aggr
(maxstring('') &
if
(
count({<[Task Name]={'WW2'},[Original Status]={'Waiting'}>}[Task Name])>0
and
count({<[Task Name]={'WW1'},[Original Status]={'Complete'}>}[Task Name])>0
,'Yes'
,'No')
,ORDER_ID
)

Lobo77
Contributor III
Contributor III
Author

Thanks so much  Gemenoutis !! Perfect .I will be able to utilise  this for many functions ! 

saikripa
Contributor III
Contributor III

Hello,

I have date columns in 4 tables and I have graphs related to all of them in the sheet. I want add a date filter common for all the dates. How do I apply the aggr formula for that. 

table 1 - contains  - col - aDate

table 2 - contains col - bDate

table 3 contains  col cDate

table 4 contains  col dDate .

I want to display results by applying just the date filter. The date columns are different and cannot be joined. The 4 table to are associated to another table(table 5)