topic Re: Only show if both Values are Present in QlikView

Only show if both Values are Present

cbaqir — Wed, 12 Sep 2018 21:05:58 GMT

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

Re: Only show if both Values are Present

swuehl — Wed, 12 Sep 2018 21:14:12 GMT

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)

Re: Only show if both Values are Present

cbaqir — Wed, 12 Sep 2018 21:40:41 GMT

Thanks. How would I apply that here?

Median Days:

Re: Only show if both Values are Present

swuehl — Wed, 12 Sep 2018 21:50:42 GMT

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.

Re: Only show if both Values are Present

BalaBhaskar_Qlik — Thu, 13 Sep 2018 09:26:08 GMT

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