topic Re: Rolling 12 Month Graph with hard coded field selections in QlikView

Rolling 12 Month Graph with hard coded field selections

cbaqir — Tue, 29 Aug 2017 19:23:24 GMT

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'}>} 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({$}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:

Message was edited by: Cassandra Baqir

Re: Rolling 12 Month Graph with hard coded field selections

cbaqir — Wed, 30 Aug 2017 14:59:18 GMT

stalwar1‌

Re: Rolling 12 Month Graph with hard coded field selections

sunny_talwar — Wed, 30 Aug 2017 15:08:35 GMT

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)

Re: Rolling 12 Month Graph with hard coded field selections

cbaqir — Wed, 30 Aug 2017 15:14:28 GMT

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.

Re: Rolling 12 Month Graph with hard coded field selections

cbaqir — Wed, 30 Aug 2017 17:20:15 GMT

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

Re: Rolling 12 Month Graph with hard coded field selections

cbaqir — Wed, 30 Aug 2017 17:28:24 GMT

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

Re: Rolling 12 Month Graph with hard coded field selections

sunny_talwar — Wed, 30 Aug 2017 17:46:04 GMT

Checking it now

Re: Rolling 12 Month Graph with hard coded field selections

sunny_talwar — Wed, 30 Aug 2017 18:46:17 GMT

Check the attached

Re: Rolling 12 Month Graph with hard coded field selections

cbaqir — Wed, 30 Aug 2017 18:48:14 GMT

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!

Re: Rolling 12 Month Graph with hard coded field selections

cbaqir — Wed, 30 Aug 2017 18:58:44 GMT

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

Re: Rolling 12 Month Graph with hard coded field selections

sunny_talwar — Wed, 30 Aug 2017 19:05:20 GMT

Checking

Re: Rolling 12 Month Graph with hard coded field selections

sunny_talwar — Wed, 30 Aug 2017 19:10:39 GMT

See if this works... Not 100% sure if it gives you what you want or not

Re: Rolling 12 Month Graph with hard coded field selections

cbaqir — Wed, 30 Aug 2017 19:13:53 GMT

Thanks, looking now! Why use CLOSE_DATE and MONTH_1?

Re: Rolling 12 Month Graph with hard coded field selections

cbaqir — Wed, 30 Aug 2017 19:14:58 GMT

Any idea why September shows 177 on top but 172 on the bottom?

Re: Rolling 12 Month Graph with hard coded field selections

sunny_talwar — Wed, 30 Aug 2017 19:23:22 GMT

They match now

Re: Rolling 12 Month Graph with hard coded field selections

cbaqir — Wed, 30 Aug 2017 19:27:26 GMT

You are my new best friend!

Re: Rolling 12 Month Graph with hard coded field selections

sunny_talwar — Wed, 30 Aug 2017 19:29:27 GMT

I know that