Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Partner
Partner

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

18 Replies
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..

Partner
Partner

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.

Contributor III
Contributor III

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

 

Contributor III
Contributor III

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

 

Partner
Partner

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.

Contributor III
Contributor III

    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

Partner
Partner

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?

Contributor III
Contributor III

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.

Contributor III
Contributor III

  • 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