Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

cbaqirdhds
Contributor 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!

Tags (3)
1 Solution

Accepted Solutions
antoniotiman
Honored Contributor III

Re: Count Rolling 6 Months

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

8 Replies
antoniotiman
Honored Contributor III

Re: Count Rolling 6 Months

Hi Cassandra,

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

Regards,

Antonio

cbaqirdhds
Contributor II

Re: Count Rolling 6 Months

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

antoniotiman
Honored Contributor III

Re: Count Rolling 6 Months

This is format of PROCESS_STEP_START_DATE

cbaqirdhds
Contributor II

Re: Count Rolling 6 Months

Doesn't appear to have made a difference.

antoniotiman
Honored Contributor III

Re: Count Rolling 6 Months

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

cbaqirdhds
Contributor II

Re: Count Rolling 6 Months

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

cbaqirdhds
Contributor II

Re: Count Rolling 6 Months

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
Honored Contributor III

Re: Count Rolling 6 Months

What means 'combine it with ' ?

OR operator ?

maybe

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

Community Browser