topic Re: Set Analysis Syntax in QlikView

Set Analysis Syntax

cbaqir — Wed, 03 Jan 2018 21:24:56 GMT

My OLD Chart is not showing the right date on the X-Axis. When I update it in the WIP chart, I lose my look back of 8 weeks. How do I update both expressions to only show the last 8 weeks like the OLD chart and still include the logic from the old expressions on PROCESS_STEP = {'Submitted to Routing', 'Routed'}, IS_IN_PROD = {'Y'}?

An example if DEFECT_ID = 9229 and it should show Avg Days = 2 and Ticket = 1 but without having to select PROCESS_STEP.

OLD and incorrect Dimension: =date(WEEK_START, 'MM-DD-YY')

OLD Expression for # Tickets: (Count({$<
PROCESS_STEP = {'Submitted to Routing', 'Routed'},
IS_IN_PROD = {'Y'},
MONTH_1 = {"$(='>=' & Date(MonthStart(Max({<PROCESS_STEP = {'Submitted to Routing', 'Routed'}>}MONTH_1), -2), 'MM-YY') & '<' & Date(MonthStart(Max({<PROCESS_STEP = {'Submitted to Routing', 'Routed'}>}MONTH_1), 0), 'MM-YY'))"}>} DISTINCT
DEFECT_ID))

OLD Expression for Avg Days: sum({$<
PROCESS_STEP = {'Submitted to Routing', 'Routed'},
IS_IN_PROD = {'Y'},
MONTH_1 = {"$(='>=' & Date(MonthStart(Max({<PROCESS_STEP = {'Submitted to Routing', 'Routed'}>}MONTH_1), -2), 'MM-YY') & '<' & Date(MonthStart(Max({<PROCESS_STEP = {'Submitted to Routing', 'Routed'}>}MONTH_1), 0), 'MM-YY'))"}>}
Aggr(Max({$<
PROCESS_STEP = {'Submitted to Routing', 'Routed'},
IS_IN_PROD = {'Y'},
MONTH_1 = {"$(='>=' & Date(MonthStart(Max({<PROCESS_STEP = {'Submitted to Routing', 'Routed'}>}MONTH_1), -2), 'MM-YY') & '<' & Date(MonthStart(Max({<PROCESS_STEP = {'Submitted to Routing', 'Routed'}>}MONTH_1), 0), 'MM-YY'))"}>}
PROCESS_STEP_START_DATE) -
Min({$<
PROCESS_STEP = {'Submitted to Routing', 'Routed'},
IS_IN_PROD = {'Y'},
MONTH_1 = {"$(='>=' & Date(MonthStart(Max({<PROCESS_STEP = {'Submitted to Routing', 'Routed'}>}MONTH_1), -2), 'MM-YY') & '<' & Date(MonthStart(Max({<PROCESS_STEP = {'Submitted to Routing', 'Routed'}>}MONTH_1), 0), 'MM-YY'))"}>}
PROCESS_STEP_START_DATE)
, DEFECT_ID))/
(Count({$<
PROCESS_STEP = {'Submitted to Routing', 'Routed'},
IS_IN_PROD = {'Y'},
MONTH_1 = {"$(='>=' & Date(MonthStart(Max({<PROCESS_STEP = {'Submitted to Routing', 'Routed'}>}MONTH_1), -2), 'MM-YY') & '<' & Date(MonthStart(Max({<PROCESS_STEP = {'Submitted to Routing', 'Routed'}>}MONTH_1), 0), 'MM-YY'))"}>} DISTINCT
DEFECT_ID))

New Dimension: =weekstart(PROCESS_STEP_START_DATE)

Expression for # Tickets:

Expression for Avg Days:

Re: Set Analysis Syntax

sunny_talwar — Wed, 03 Jan 2018 23:37:23 GMT

WIP Chart doesn't seem to have any set analysis to show 8 weeks... not sure what you are hoping for?

Re: Set Analysis Syntax

cbaqir — Thu, 04 Jan 2018 14:18:03 GMT

I want that chart to show 8 weeks along with the hard coded options from old including PROCESS_STEP = {'Submitted to Routing', 'Routed'}, IS_IN_PROD = {'Y'}, and the Max date logic. I tried just switching out MONTH_1 for PROCESS_STEP_START_DATE but that didn't fix it.

Re: Set Analysis Syntax

sunny_talwar — Thu, 04 Jan 2018 15:27:03 GMT

Can you share what you tried?

Re: Set Analysis Syntax

cbaqir — Thu, 04 Jan 2018 15:29:28 GMT

sum({$<
PROCESS_STEP = {'Submitted to Routing', 'Routed'},
IS_IN_PROD = {'Y'}
,PROCESS_STEP_START_DATE = {"$(='>=' & Date(AddMonths(Max(PROCESS_STEP_START_DATE), -2), 'MM-DD-YY') & '<' & Date(AddMonths(Max(PROCESS_STEP_START_DATE), 1), 'MM-DD-YY'))"}
>}
Aggr(Max({$<
PROCESS_STEP = {'Submitted to Routing', 'Routed'},
IS_IN_PROD = {'Y'}
,PROCESS_STEP_START_DATE = {"$(='>=' & Date(AddMonths(Max(PROCESS_STEP_START_DATE), -2), 'MM-DD-YY') & '<' & Date(AddMonths(Max(PROCESS_STEP_START_DATE), 1), 'MM-DD-YY'))"}
>}
PROCESS_STEP_START_DATE) -
Min({$<
PROCESS_STEP = {'Submitted to Routing', 'Routed'},
IS_IN_PROD = {'Y'}
,PROCESS_STEP_START_DATE = {"$(='>=' & Date(AddMonths(Max(PROCESS_STEP_START_DATE), -2), 'MM-DD-YY') & '<' & Date(AddMonths(Max(PROCESS_STEP_START_DATE), 1), 'MM-DD-YY'))"}
>}
PROCESS_STEP_START_DATE)
, DEFECT_ID))
/(Count({$<
PROCESS_STEP = {'Submitted to Routing', 'Routed'},
IS_IN_PROD = {'Y'}
,PROCESS_STEP_START_DATE = {"$(='>=' & Date(AddMonths(Max(PROCESS_STEP_START_DATE), -2), 'MM-DD-YY') & '<' & Date(AddMonths(Max(PROCESS_STEP_START_DATE), 1), 'MM-DD-YY'))"}
>} DISTINCT
DEFECT_ID))

Re: Set Analysis Syntax

sunny_talwar — Thu, 04 Jan 2018 15:43:59 GMT

Can you rerun the app after creating a new field in the script

WeekStart(PROCESS_STEP_START_DATE) as WeekStart_PROCESS_STEP_START_DATE

Re: Set Analysis Syntax

cbaqir — Thu, 04 Jan 2018 15:56:31 GMT

Updated

Re: Set Analysis Syntax

cbaqir — Thu, 04 Jan 2018 20:21:46 GMT

I fixed part of it but broke another... If I use this for the Avg # days, I see 8 weeks but the PROCESS_STEP and IS_IN_PROD selections are not hardcoded. Any ideas? Something to do with the dimension?

sum({$<
PROCESS_STEP = {'Submitted to Routing', 'Routed'},
IS_IN_PROD = {'Y'}
,PROCESS_STEP_START_DATE={">=$(=WeekStart(Max(PROCESS_STEP_START_DATE), -8))<=$(=Date(Max(PROCESS_STEP_START_DATE)))"}
>}
Aggr(Max({$<
PROCESS_STEP = {'Submitted to Routing', 'Routed'},
IS_IN_PROD = {'Y'}

,PROCESS_STEP_START_DATE={">=$(=WeekStart(Max(PROCESS_STEP_START_DATE), -8))<=$(=Date(Max(PROCESS_STEP_START_DATE)))"}
>}
PROCESS_STEP_START_DATE) -
Min({$<
PROCESS_STEP = {'Submitted to Routing', 'Routed'},
IS_IN_PROD = {'Y'}
,PROCESS_STEP_START_DATE={">=$(=WeekStart(Max(PROCESS_STEP_START_DATE), -8))<=$(=Date(Max(PROCESS_STEP_START_DATE)))"}
>}
PROCESS_STEP_START_DATE)
, DEFECT_ID))
/(Count({$<
PROCESS_STEP = {'Submitted to Routing', 'Routed'},
IS_IN_PROD = {'Y'}
,PROCESS_STEP_START_DATE={">=$(=WeekStart(Max(PROCESS_STEP_START_DATE), -8))<=$(=Date(Max(PROCESS_STEP_START_DATE)))"}
>} DISTINCT
DEFECT_ID))

Re: Set Analysis Syntax

sunny_talwar — Mon, 08 Jan 2018 00:06:33 GMT

Where do you see it working in the attached application? I don't see it working in any of the charts from the attached application

Re: Set Analysis Syntax

cbaqir — Mon, 08 Jan 2018 14:28:24 GMT

I uploaded a new version with the code in the last message. That's the closest I can get.

Re: Set Analysis Syntax

cbaqir — Mon, 08 Jan 2018 14:29:31 GMT

One of the additional problems is that I have a lot of date differences that end up as 0 so there is no bar for that month and you also can't see the number of tickets.

Re: Set Analysis Syntax

sunny_talwar — Mon, 08 Jan 2018 16:31:49 GMT

But where is the chart that uses the expression? Sorry, but I am not able to find it... would you be able to take a snapshot and let me know where exactly am I looking

Re: Set Analysis Syntax

cbaqir — Mon, 08 Jan 2018 16:39:24 GMT

https://community.qlik.com/servlet/JiveServlet/download/1411638-309920/KPI_Avgdays.qvw