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

Rolling 12 Month Graph with hard coded field selections

I am trying to take these 2 expressions and modify it to only show the previous 12 months (where CLOSE_DATE is within the last 12 months) and need some help:

Count:

Count({$<DEFECT_ID = {"=Count({<PROCESS_STEP = {'Ticket Created (Detected)', 'Ticket Closed'}, IS_IN_PROD = {'Y'}>}PROCESS_STEP) = GetSelectedCount(PROCESS_STEP)"}, PROCESS_STEP = {'Ticket Created (Detected)', 'Ticket Closed'}, IS_IN_PROD = {'Y'}>} DISTINCT DEFECT_ID)

Avg Days:

SUM({$<DEFECT_ID *= {"=Min({$}CLOSE_DATE) >= AddMonths(Today(),-12)"},PROCESS_STEP = {'Ticket Created (Detected)', 'Ticket Closed'}, IS_IN_PROD = {'Y'}>} AggrMax({$<PROCESS_STEP = {'Ticket Created (Detected)', 'Ticket Closed'},
IS_IN_PROD = {'Y'}>}PROCESS_STEP_START_DATE) - Min({$<PROCESS_STEP = {'Ticket Created (Detected)', 'Ticket Closed'},
IS_IN_PROD = {'Y'}>} PROCESS_STEP_START_DATE), DEFECT_ID)) /
(
Count({$<DEFECT_ID *= {"=Min({$}CLOSE_DATE) >= AddMonths(Today(),-12)"},PROCESS_STEP = {'Ticket Created (Detected)', 'Ticket Closed'}, IS_IN_PROD = {'Y'},
DEFECT_ID = {"=Count({$<PROCESS_STEP = {'Ticket Created (Detected)', 'Ticket Closed'}, IS_IN_PROD = {'Y'}>} PROCESS_STEP) = GetSelectedCount(PROCESS_STEP)"}>} DISTINCT DEFECT_ID))

The numbers in the bottom graphs should match the numbers in the top graphs:

8-30-2017 10-28-51 AM.jpg

Message was edited by: Cassandra Baqir

1 Solution

Accepted Solutions
sunny_talwar

16 Replies
cbaqir
Specialist II
Specialist II
Author

sunny_talwar

I am trying to take these 2 expressions and modify it to only show the previous 12 months (where CLOSE_DATE is within the last 12 months) and need some help:

What do you mean when you say where CLOSE_DATE is within the last 12 months? What is the expected output based on the sample provided (with or without selections)

cbaqir
Specialist II
Specialist II
Author

The expected output is what is in this image (also includes IS_IN_PROD = Y):

These two graphs are dependent on making two selections. The graphs I need help with have those selections hard-coded because I use them for Nprinting.

What I am looking for are tickets that were CLOSED in the specified month within the last 12 months. If we needed to add a date field, it would be CLOSE_DATE.

8-30-2017 10-18-42 AM.jpg

cbaqir
Specialist II
Specialist II
Author

Edited image for expected output because I forgot that IS_IN_PROD was also hard coded.

cbaqir
Specialist II
Specialist II
Author

I updated the QVW sample and screen shots. Does that make it more clear?

sunny_talwar

Checking it now

sunny_talwar

Check the attached

cbaqir
Specialist II
Specialist II
Author

It's the bottom charts I am trying to see the rolling 12 months in. The ones with the process steps and is_in_prod hardcoded.

I will try adding MONTH_1 = {"$(='>=' & Date(MonthStart(Max(MONTH_1), -11), 'MM-YY') & '<' & Date(MonthStart(Max(MONTH_1), 1), 'MM-YY'))"} to them. Thanks!

cbaqir
Specialist II
Specialist II
Author

How do we apply the same logic to the bottom Avg # Days chart with the values hardcoded?

SUM({$<DEFECT_ID *= {"=Min({$}PROCESS_STEP_START_DATE) >= AddMonths(Today(),-12)"},
PROCESS_STEP = {'Ticket Created (Detected)', 'Ticket Closed'}, IS_IN_PROD = {'Y'}>} Aggr( Max({$<PROCESS_STEP = {'Ticket Created (Detected)', 'Ticket Closed'},
IS_IN_PROD = {'Y'}>}PROCESS_STEP_START_DATE) - Min({$<PROCESS_STEP = {'Ticket Created (Detected)', 'Ticket Closed'},
IS_IN_PROD = {'Y'}>} PROCESS_STEP_START_DATE), DEFECT_ID)) /
(Count({$<DEFECT_ID *= {"=Min({$}PROCESS_STEP_START_DATE) >= AddMonths(Today(),-12)"},
PROCESS_STEP = {'Ticket Created (Detected)', 'Ticket Closed'}, IS_IN_PROD = {'Y'},
DEFECT_ID = {"=Count({$<PROCESS_STEP = {'Ticket Created (Detected)', 'Ticket Closed'},
IS_IN_PROD = {'Y'}>} PROCESS_STEP) = GetSelectedCount(PROCESS_STEP)"}>} DISTINCT DEFECT_ID))