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

 

 

1 Solution

Accepted Solutions
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
)

View solution in original post

21 Replies
trdandamudi
Master II
Master II

In a straight table use the below set expression and then hide the expression column:

=Count({<[Order ID]=p({<[DIM 1]={'T1'},[DIM 2]={'WAITING'},[DIM 3]={'ALWAYS'}>}) * p({<[DIM 1]={'T2'},[DIM 2]={'COMPLETE'},[DIM 3]={'ALWAYS'}>})>} [Order ID])

 

multiple dimensions.PNG

Hope this helps..

gmenoutis
Partner - Creator II
Partner - Creator II

You can use a calculated dimension outside the script. There are a few caveats:

  • Calculated dimensions do not appear in the selection tool.
  • Calculated dimensions actually apply a filter to another, existing field through an aggr() formula. You will have to think well which field has the maximum granularity for what you want. In your example, it is [Order ID]. So, you could have you own calculated dimension which would be something like:

aggr(
maxstring('') &
if
(
([DIM 1]='T1' and [DIM 2]='WAITING' and [DIM 3]='ALWAYS')
or
([DIM 1]='T2' and [DIM 2]='COMPLETE' and [DIM 3]='ALWAYS')
,'Yes'
,'No'
,[Order ID]
)

This will cause you to be able to click on the Yes/No values, which will make a selection on the Order IDs.

Lobo77
Contributor III
Contributor III
Author

This looks like what I am after.

I used your formula below , few different ways ,  but the dimension by default displayed 'no' , and only  displayed 'yes' when  I selected orders that had the conditions I am after. I could not filter by y/n .

Note , the data has a lot of other dimensions , do I need to some how also exclude any other dimensions not listed in the formula and only include the conditions required ? 

 

Thanks again

 

Lobo77
Contributor III
Contributor III
Author

Thanks Trdandamudi.

Tried expression , but did get invalid dimension. Tried a few different ways.  Does not look like I have straight table in the list  ? ( box & pivot ) , so don't think I could hide the dimension .

 I know it can work  because I can see yours worked in the table below.

 

Will keep trying 🙂

Thanks

 

gmenoutis
Partner - Creator II
Partner - Creator II

This should have worked regardless of other dimensions, as long as the maximum granularity is [Order ID].

So you are saying you are getting 'Yes' and 'No' for the same [Order ID] depending on selections? This is strange. Are you sure you are using a calculated dimension? Could you provide a screenshot, or even better, a .qvf? I think this behaviour appears with measures that have aggr() on table objects.

Lobo77
Contributor III
Contributor III
Author

    Hi Gmenoutis. Wont be back in the office for a few days , will get some screen shots then. In the meantime , for example, (1st pic) when I had an order that had the conditions in the calculated expression,  with nothing filtered , it would display 'no' ( 'yes' is not displayed , or as a choice ) . If I filtered that same order down to the conditions required manually  ( symboled by #)  , it would then display yes ( 2nd pic ) .

I did not create the calculated expression as a master item ( don't have profile for that ) , I added a dimension to the table via the FX editor. It's a sheet within a published app. Is that the same as a calculated dimension ?

PIC #1

Order ID

DIM 1

DIM 2

DIM 3

CALCUTAED DIM

1234

T1

WAITING

ALWAYS

NO

1234

T2

COMPLETE

ALWAYS

NO

1234

T3

WAITING

ALWAYS

NO

1234

T4

NOT STARTED

ALWAYS

NO

1234

T5

WAITING

ALWAYS

NO

1234

T6

COMPLETE

SOMETIMES

NO

1234

T7

SUSPENDED

SOMETIMES

NO

1234

T8

COMPLETE

ALWAYS

NO

 

PIC#2

Order ID

DIM 1#

DIM 2

DIM 3

Calculated DIM

1234

T1

WAITING

ALWAYS

YES

1234

T2

COMPLETE

ALWAYS

YES

 

 

Thanks again

gmenoutis
Partner - Creator II
Partner - Creator II

That behaviour still makes me think you have created a measure, and not a calculated dimension. Does it have a magnifying glass in the top of the column?

Lobo77
Contributor III
Contributor III
Author

Hi Gmenitous. Yes , there is a magnifying glass at the top of the column. To add the formula , I selected : add dimension ( in the table , and tried in a filter) : selected  FX , then entered the formula. The magnifying gives the option of 'no' , until I filter down to the conditions , then it displays 'yes'. Will send screen shots when back in office.

Thanks again.

Lobo77
Contributor III
Contributor III
Author

  • Hi gmenoutis , screen shots below , cant share data though unfortunately.
  • Have tried so many varitions of the script , still no joy.

Let me know if see anything that may help.

Cheers

 

Pictures below :

  1. added filter
  2. added dimension via FX
  3. Added expression    [ To make the expression work , had to add a ‘)’ – see circled red in picture below]
  4. No option for yes , however there are 3000 + orders with the conditions

        5) If I filter some of the tasks – yes becomes and option – however not to all orders

 

 

  1.  

clipboard_image_0.png

 

2)

clipboard_image_1.png

 

 

 

 

3) To make the expression work , had to add a ‘)’ – see circled red.

 

clipboard_image_2.png

 

4) – No option for yes , however there are 3000 + orders with the conditions

 

clipboard_image_3.png

 

5) If I filter some of the tasks – yes becomes and option – however not to all orders

clipboard_image_4.png