Create buckets based on Outstanding, Completed and Hold

[#1]

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.

[Frank_Hartmann]

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

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

[#3]

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

[njmaehler]

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'

[Anonymous]

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

[#6]

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

[Anonymous]

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!