Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cbaqir
Specialist II
Specialist II

Count Rolling 6 Months

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!

1 Solution

Accepted Solutions
antoniotiman
Master III
Master III

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

View solution in original post

8 Replies
antoniotiman
Master III
Master III

Hi Cassandra,

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

Regards,

Antonio

cbaqir
Specialist II
Specialist II
Author

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

antoniotiman
Master III
Master III

This is format of PROCESS_STEP_START_DATE

cbaqir
Specialist II
Specialist II
Author

Doesn't appear to have made a difference.

antoniotiman
Master III
Master III

=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
Specialist II
Specialist II
Author

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

cbaqir
Specialist II
Specialist II
Author

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
Master III
Master III

What means 'combine it with ' ?

OR operator ?

maybe

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