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 |
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?
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
)
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
)
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 ?
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)
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_ID | Task Name | Original Status | Result | Expected result |
37496752 | WW1 | Completed | YES | YES |
37496752 | WW2 | Waiting | YES | YES |
37123752 | WW1 | Waiting | YES | Should be NO |
37123752 | WW2 | Waiting | YES | Should be NO |
47124552 | WW1 | Completed | YES | Should be NO |
47124552 | WW2 | Completed | YES | Should be NO |
Thanks Gmenoutis. Have tried a few diffrent ways now , still no joy.
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
)
Thanks so much Gemenoutis !! Perfect .I will be able to utilise this for many functions !
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)