Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
)
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])
Hope this helps..
You can use a calculated dimension outside the script. There are a few caveats:
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.
Thanks gmenoutis.
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
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
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.
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
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?
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.
Let me know if see anything that may help.
Cheers
Pictures below :
5) If I filter some of the tasks – yes becomes and option – however not to all orders
2)
3) To make the expression work , had to add a ‘)’ – see circled red.
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