topic Re: Count Rolling 6 Months in QlikView

Count Rolling 6 Months

cbaqir — Mon, 24 Jul 2017 22:12:53 GMT

I need to show a variety of metrics based on volume of DEFECT_ID within the last 6 months.

I started with: =Count({$<PROCESS_STEP={'Ticket Created (Detected)'}>}DEFECT_ID)

but when I add the addmonths function, it doesn't work.

=Count({$<PROCESS_STEP_START_DATE={">=$(=Date(addmonths(monthend(today()),-6),'MM-DD-YYYY'))<=$(=Date(addmonths(monthend(today()),0),'MM-DD-YYYY'))"},PROCESS_STEP={'Ticket Created (Detected)'}>} distinct DEFECT_ID)

I would expect to see 146.

Any ideas?

TIA!

Re: Count Rolling 6 Months

antoniotiman — Mon, 24 Jul 2017 22:30:25 GMT

Hi Cassandra,

change 'MM-DD-YYYY' to 'MM/DD/YYYY' in Set Analysis.

Regards,

Antonio

Re: Count Rolling 6 Months

cbaqir — Tue, 25 Jul 2017 15:00:55 GMT

I'm not sure why that would make a difference.

Re: Count Rolling 6 Months

antoniotiman — Tue, 25 Jul 2017 15:08:10 GMT

This is format of PROCESS_STEP_START_DATE

Re: Count Rolling 6 Months

cbaqir — Tue, 25 Jul 2017 15:20:45 GMT

Doesn't appear to have made a difference.

Re: Count Rolling 6 Months

antoniotiman — Tue, 25 Jul 2017 15:24:48 GMT

=Count({$<PROCESS_STEP_START_DATE={">=$(=Date(addmonths(monthend(today()),-6),'MM/DD/YYYY'))<=$(=Date(addmonths(monthend(today()),0),'MM/DD/YYYY'))"},PROCESS_STEP={'Ticket Created (Detected)'}>} distinct DEFECT_ID)

Re: Count Rolling 6 Months

cbaqir — Tue, 25 Jul 2017 15:26:05 GMT

Wow. I didn't think that the date format would affect this. Thanks!

Re: Count Rolling 6 Months

cbaqir — Tue, 25 Jul 2017 17:06:27 GMT

What about combining this logic with another step? So, take this:

and combine it with: Of those created within the last 6 months, how many touched PROCESS_STEP={'Submitted to Routing'} ?

Re: Count Rolling 6 Months

antoniotiman — Tue, 25 Jul 2017 17:48:42 GMT

What means 'combine it with ' ?

OR operator ?

maybe

PROCESS_STEP={'Ticket Created (Detected)','Submitted to Routing'}