Only show if both Values are Present

[cbaqir]

My chart should only show Median days and # of tickets for those where DEFECT_ID went to both the "Submitted to APT" and "Submitted to Routing" Steps. I see a lot of tickets that went through "Submitted to Routing" but NOT  "Submitted to APT".

How do I need to modify the expression?

Median Days:

=Median({$<
PROCESS_STEP = {'Submitted to ART','Submitted to Routing'}
>}
Aggr(Max({$<
PROCESS_STEP = {'Submitted to ART','Submitted to Routing'}
>}
PROCESS_STEP_START_DATE) -
Min({$<
PROCESS_STEP = {'Submitted to ART','Submitted to Routing'}
>}
PROCESS_STEP_START_DATE)
, DEFECT_ID))

# Tickets:

(Count({$<
PROCESS_STEP = {'Submitted to ART','Submitted to Routing'}
>} DISTINCT
DEFECT_ID))

[swuehl]

I think you need the intersection of the steps searched per defect id, something like

Count(

{<

DEFECT_ID =

     p({<PROCESS_STEP = {'Submitted to ART'}>})*p({<PROCESS_STEP = {'Submitted to Routing'}>})

>}

DISTINCT DEFECT_ID)

[cbaqir]

Thanks. How would I apply that here?

Median Days:

=Median({$<
PROCESS_STEP = {'Submitted to ART','Submitted to Routing'}
>}
Aggr(Max({$<
PROCESS_STEP = {'Submitted to ART','Submitted to Routing'}
>}
PROCESS_STEP_START_DATE) -
Min({$<
PROCESS_STEP = {'Submitted to ART','Submitted to Routing'}
>}
PROCESS_STEP_START_DATE)
, DEFECT_ID))

[swuehl · Accepted solution]

Not quite sure if I understand your requirement correctly, maybe something like

=Median({$<

DEFECT_ID = p({<PROCESS_STEP = {'Submitted to ART'}>})*p({<PROCESS_STEP = {'Submitted to Routing'}>})

>}

Aggr(Max({$<

PROCESS_STEP = {'Submitted to ART','Submitted to Routing'}, DEFECT_ID = p({<PROCESS_STEP = {'Submitted to ART'}>})*p({<PROCESS_STEP = {'Submitted to Routing'}>})

>}

PROCESS_STEP_START_DATE) -

Min({$<

PROCESS_STEP = {'Submitted to ART','Submitted to Routing'}, DEFECT_ID = p({<PROCESS_STEP = {'Submitted to ART'}>})*p({<PROCESS_STEP = {'Submitted to Routing'}>})

>}

PROCESS_STEP_START_DATE)

, DEFECT_ID))

But you seem to use the expression in a chart context with a calculated Year / Month, so you might need to create a field in your script for that and add this to your aggr() dimensions.

Or if you need to check if both values are present for the combination of defect ID and YearMonth, you need to create a combined key field in the script to use in the set expression.

[balabhaskarqlik]

May be this:

=Median({$<PROCESS_STEP = {'Submitted to ART'}> * <PROCESS_STEP = {'Submitted to Routing'}>}

Aggr(Max({$<PROCESS_STEP = {'Submitted to ART'}> * <PROCESS_STEP = {'Submitted to Routing'}>}PROCESS_STEP_START_DATE) -

     Min({$<PROCESS_STEP = {'Submitted to ART'}> * <PROCESS_STEP = {'Submitted to Routing'}>}PROCESS_STEP_START_DATE), DEFECT_ID))