Count Rolling 6 Months

[cbaqir]

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!

[antoniotiman]

Hi Cassandra,

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

Regards,

Antonio

[cbaqir]

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

[antoniotiman]

This is format of PROCESS_STEP_START_DATE

[cbaqir]

Doesn't appear to have made a difference.

[antoniotiman · Accepted solution]

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

[cbaqir]

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

[cbaqir]

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

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

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

[antoniotiman]

What means 'combine it with ' ?

OR operator ?

maybe

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