Qlik Community

Ask a Question

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Specialist
Specialist

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

Hi Cassandra,

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

Regards,

Antonio

Specialist
Specialist

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

Master III
Master III

This is format of PROCESS_STEP_START_DATE

Specialist
Specialist

Doesn't appear to have made a difference.

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

Specialist
Specialist

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

Specialist
Specialist

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'} ?

Master III
Master III

What means 'combine it with ' ?

OR operator ?

maybe

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