topic Re: AND-Mode Days Between Calculation in QlikView

AND-Mode Days Between Calculation

cbaqir — Mon, 24 Sep 2018 19:48:57 GMT

I am currently using the AND-Mode option but I noticed that while the correct ticket (DEFECT_ID) numbers are showing, the calculation between the selected dates is not.

When RES_STATUS_AND_MODE Ready for Validation Prod and Validated Successfully Prod are selected for DEFECT_ID 11112, I should see a turnaround time of 0 days. However, When RES_STATUS Ready for Validation Prod and Validated Successfully Prod are selected for DEFECT_ID 11112, I see a turnaround time of 0 days but this is an OR selection and does not show those tickets that went through Both statuses.

The calculated expression value is 20:

SUM(
Aggr(
Max(RES_STATUS_START_DATE)- Min(RES_STATUS_START_DATE)
,DEFECT_ID)
)
/Count({$}DISTINCT DEFECT_ID)

Re: AND-Mode Days Between Calculation

swuehl — Mon, 24 Sep 2018 21:37:34 GMT

I am not sure I understand.

If status X and Y is fullfilled for a certain ticket, then X or Y is also fulfilled.

Maybe you are thinking of an exclusive or, XOR?

Re: AND-Mode Days Between Calculation

cbaqir — Mon, 24 Sep 2018 21:40:30 GMT

I need to show the average turnaround time for the 2 selected (RES_STATUS_AND_MODE) steps based on the difference between the RES_STATUS_START_DATE.

From what I read on AND-Mode , this is to show only the tickets that hit both statuses as opposed to one or the other.

Re: AND-Mode Days Between Calculation

swuehl — Mon, 24 Sep 2018 22:03:50 GMT

I can't open your QVW right now, but keep in mind that selecting in the AND mode status field does only filter the defect id, it does not filter your status field connected with the start date (at least that's how I assume you've built your model).

Re: AND-Mode Days Between Calculation

swuehl — Mon, 24 Sep 2018 22:06:04 GMT

Maybe try something like

SUM(
Aggr(
Max({<RES_STATUS = p(RES_STATUS_AND_MODE)>} RES_STATUS_START_DATE)- Min({<RES_STATUS = p(RES_STATUS_AND_MODE)>} RES_STATUS_START_DATE)
,DEFECT_ID)
)
/Count({$}DISTINCT DEFECT_ID)

Re: AND-Mode Days Between Calculation

cbaqir — Tue, 25 Sep 2018 14:13:38 GMT

Yes, I think that is the issue but I don't know how to resolve it. This is a very important requirement for my end user.

Re: AND-Mode Days Between Calculation

cbaqir — Tue, 25 Sep 2018 14:14:47 GMT

I get this in theory but it did not work.

Re: AND-Mode Days Between Calculation

swuehl — Tue, 25 Sep 2018 14:24:54 GMT

Maybe this:

SUM(

Aggr(

Max({<RES_STATUS = $::RES_STATUS_AND_MODE >} RES_STATUS_START_DATE)- Min({<RES_STATUS = $::RES_STATUS_AND_MODE >} RES_STATUS_START_DATE)

,DEFECT_ID)

)

/Count({$}DISTINCT DEFECT_ID)

Re: AND-Mode Days Between Calculation

cbaqir — Tue, 25 Sep 2018 14:34:58 GMT

Thank you soooo much! I will have to look to find out what = $:: does.

Re: AND-Mode Days Between Calculation

swuehl — Tue, 25 Sep 2018 14:38:30 GMT

That's the scope operator referencing the default state $.

I believe you can actually remove the scope operator (the $:: part), it should work the same in your case.

Re: AND-Mode Days Between Calculation

cbaqir — Wed, 26 Sep 2018 14:43:56 GMT

I am trying to show a table with the associated statuses and dates but I can't figure out why they aren't showing up correctly. If I have selected 2 statuses, I would want to see only those and their associated start dates. Any suggestions?

Re: AND-Mode Days Between Calculation

swuehl — Wed, 26 Sep 2018 15:24:59 GMT

Can't see how you were trying to do this.

But maybe like this

Target Prod Release Month	DEFECT_ID	RES_STATUS	Max({<RES_STATUS = $::RES_STATUS_AND_MODE >} RES_STATUS_START_DATE)	Min({<RES_STATUS = $::RES_STATUS_AND_MODE >} RES_STATUS_START_DATE)
			09/11/18	09/11/18
Sep-2018	11112	Ready for Validation Prod	09/11/18	09/11/18
Sep-2018	11112	Validated Successfully Prod	09/11/18	09/11/18

Re: AND-Mode Days Between Calculation

cbaqir — Thu, 27 Sep 2018 21:58:16 GMT

Thanks. So when I disable the Avg expression, I only see the 2 rows.