topic Re: Nested Set Analysis in QlikView

Nested Set Analysis

Wed, 25 Nov 2020 16:16:04 GMT

I have a problem regarding the Nested Set Analysis below:

DB:

WorkOrderID	WorkOrder_Date	Machine	Install_Date
WO-1	201508	Machine1	201502
WO-2	201509	Machine2	201503
WO-3	201510	Machine3	201504
WO-4	201511	Machine4	201505
WO-5	201512	Machine5	201506
WO-6	201601	Machine6	201507
WO-7	201602	Machine7	201508
WO-8	201603	Machine8	201509

I need to calculate the Past three Months WorkOrder QTY which the Machine Install_date must be the Past six Months.

Calculation Condition: the Machine Install_date must be the Past six Months for WorkOrder.

Please help to provide the expression. Many thanks.

Re: Nested Set Analysis

sunny_talwar — Tue, 22 Mar 2016 12:38:25 GMT

What is the expected output for the above sample?

Re: Nested Set Analysis

swuehl — Tue, 22 Mar 2016 12:41:10 GMT

Maybe somthing like

=Count(DISTINCT {<WorkOrderID = {"=AddMonths(Date#(Install_Date,'YYYYMM'),5)<Date#(WorkOrder_Date,'YYYYMM')"}>} WorkOrderID)

Re: Nested Set Analysis

Tue, 22 Mar 2016 12:49:38 GMT

the WorkOrder QTY.

For example:

if I select WorkOrder_date '201603', it need to look whether the Machine Install_date is last 6 six Months, then calculate the past three related WorkOrder QTY.

Re: Nested Set Analysis

Tue, 22 Mar 2016 12:53:03 GMT

Please help to advise whether it need to add the WorkOrder_date. Because I also need to calculate the Past three Months WorkOrder .