Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Can you upload a small sample with reduced and obfuscated data?
Without knowing your script and expressions it will be hard to assist
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 |
| |||
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 |
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'
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!!!
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
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!