topic Re: Create buckets based on Outstanding, Completed and Hold in QlikView

Create buckets based on Outstanding, Completed and Hold

Wed, 25 Nov 2020 16:16:04 GMT

Hello all:

I need help creating a graph that show T-shirt size items that are Outstanding vs.. completed vs. Hold in buckets in Months

Instead of showing Jan, Feb, Mar.....

It needs to show <1 , 1-3, 4-6, 7-9, 10-12

The T-Shirt Size field contains (XS, S, M, L, XL, TBD

The Status field contains (Active, Closed, Completed, On Hold and Requested

This is how far I've gotten

AgeBucket:
LOAD *,
Interval(Now()-[Rqstd. Compl.]-30, 'MM') As DateOutstanding;
LOAD
Date(Floor(Today()-Rand()*140)) As DateCompleted, 1 As exp AutoGenerate 10000;

Below is the list of available fields

See attached file.

Re: Create buckets based on Outstanding, Completed and Hold

Frank_Hartmann — Fri, 29 Jul 2016 21:52:22 GMT

Can you upload a small sample with reduced and obfuscated data?

Without knowing your script and expressions it will be hard to assist

Re: Create buckets based on Outstanding, Completed and Hold

Mon, 01 Aug 2016 11:54:15 GMT

Hi Frank,

Here is a sample set of the data. Thank you for help.

Request#

Demand Type

Status

Date Rqstd

Rqstd. Compl.

Bus. or Dept.

Domain

T-Shirt Sz

Est. Compl. Date

Actual Compl. Date

Yr Compl. a/o Closed

Created

1755

Maintenance

Requested

7/21/2016

9/30/2016

ABC12

Sales

TBD

7/21/2016 17:38

5/18/2016

1754

Operational

Requested

7/21/2016

8/1/16 if possible

CTE12

Marketing

TBD

7/21/2016 17:32

1753

Operational

Requested

7/20/2016

8/1/2016

FJR34

Operations

TBD

7/21/2016 8:20

1752

Operational

Requested

7/19/2016

8/31/2016

CKE59

Maintenance

TBD

7/19/2016 14:29

1751

Operational

Requested

7/15/2016

asap

LSL09

Finance

TBD

7/18/2016 13:58

1750

Operational

Requested

7/7/2016

ASAP

CMK94

Human Resources

TBD

7/8/2016 8:30

1749

Compliance

Requested

7/5/2016

1/1/2017

ACI00

Supply Chain

TBD

7/6/2016 9:49

1748

Operational

Requested

7/1/2016

9/1/16

TTY90

Lab & Quality

TBD

7/6/2016 8:10

1747

Operational

Requested

6/30/2016

9/15/2016

WQI49

Maintenance

TBD

6/30/2016 16:19

1746

Operational

Requested

6/30/2016

10/30/2016

SPC48

Supply Chain

TBD

6/30/2016 16:12

Re: Create buckets based on Outstanding, Completed and Hold

njmaehler — Fri, 12 Aug 2016 09:32:49 GMT

Would CLASS not work?

class(expression, interval [ , label [ , offset ]])

Creates a classification of expressions. The bin width is determined by the number set as interval. The result is shown as a<=x<b, where a and b are the upper and lower limits of the bin. The x can be replaced by an arbitrary string stated in label. 0 is normally the default starting point of the classification. This can be changed by adding an offset.

Examples:

class( var,10 ) with var = 23 returns '20<=x<30'

class( var,5,'value' ) with var = 23 returns '20<= value <25'

class( var,10,'x',5 ) with var = 23 returns '15<=x<25'

Re: Create buckets based on Outstanding, Completed and Hold

Anonymous — Fri, 12 Aug 2016 10:49:42 GMT

Try this code Patricio:

AgeCompleted:
LOAD *,
    IF(DateOutstanding<1,'<1',
        IF(DateOutstanding<4,'1-3',
            IF(DateOutstanding<7,'4-6',
                IF(DateOutstanding<10,'7-9',
                    IF(DateOutstanding<13,'10-12','>12'))))) as TimeElapsedGroup;
LOAD
    Distinct
    [Actual Compl. Date],
    [Rqstd. Compl.],
    [Date Rqstd],
    If(Interval(IF(IsNull([Actual Compl. Date]),Now(),[Actual Compl. Date])-[Date Rqstd]-30, 'MM')<0,0,
        Interval(IF(IsNull([Actual Compl. Date]),Now(),[Actual Compl. Date])-[Date Rqstd]-30, 'MM')) As DateOutstanding
RESIDENT ITServiceRequest;

I don't know how you will calculate your elapsed time, because [Rqstd. Compl.] field is not a date.

Regards!!!

Re: Create buckets based on Outstanding, Completed and Hold

Fri, 12 Aug 2016 12:56:02 GMT

Hi Manuel,

When I inserted the code you suggested below:

LEFT Join (ITServiceRequest)
LOAD DISTINCT [Rqstd. Compl.],
Date([Rqstd. Compl.]) as [Req Compl Date]
RESIDENT ITServiceRequest;

AgeCompleted:
LOAD *,
IF(DateOutstanding<1,'<1',
IF(DateOutstanding<4,'1-3',
IF(DateOutstanding<7,'4-6',
IF(DateOutstanding<10,'7-9',
IF(DateOutstanding<13,'10-12','>12'))))) as TimeElapsedGroup;
//
//AgeBucket:
//LOAD *,
//Interval(Now()-[Rqstd. Compl.]-30, 'MM') As DateOutstanding;

//LOAD
//Date(Floor(Today()-Rand()*140)) As DateCompleted, 1 As exp AutoGenerate 10000;

LOAD
Distinct
[Actual Compl. Date] as ActComplDate,
[Rqstd. Compl.] as [Requested Completed],
[Date Rqstd] as [Date Requested],
If(Interval(IF(IsNull(ActComplDate),Now(),(ActComplDate)-[Date Requested]-30, 'MM')<0,0,
Interval(IF(IsNull(ActComplDate),Now(),(ActComplDate)-[Date Requested]-30, 'MM')) As DateOutstanding
RESIDENT ITServiceRequest;

I get the below error

Error in expression: If takes 2-3 parameters

LOAD
   Distinct
   [Actual Compl. Date] as ActComplDate,
   [Rqstd. Compl.] as [Requested Completed],
   [Date Rqstd] as [Date Requested],
   If(Interval(IF(IsNull(ActComplDate),Now(),(ActComplDate)-[Date Requested]-30, 'MM')<0,0,
        Interval(IF(IsNull(ActComplDate),Now(),(ActComplDate)-[Date Requested]-30, 'MM')) As DateOutstanding
RESIDENT ITServiceRequest

Re: Create buckets based on Outstanding, Completed and Hold

Anonymous — Tue, 16 Aug 2016 06:10:28 GMT

Hi Patricio,

Try This:

If(Interval(IF(IsNull(ActComplDate),Now(),ActComplDate)-[Date Requested]-30, 'MM')<0,0,

Interval(IF(IsNull(ActComplDate),Now(),ActComplDate)-[Date Requested]-30, 'MM')) As DateOutstanding

You have written more than allowed parentheses.

Regards!