Find my approach as reference below and stored in attached workbook version 10.3 located in the original thread.
1. M1 gg:datediff('day',[Created Date],[Closing DATE])
2. D1 Bands
if [M1. gg]<3 then "gg<3"
elseif [M1. gg]>=3 and [M1. gg]<7 then "3<=gg<7"
elseif [M1. gg]>=7 and [M1. gg]<15 then "7<=gg<15"
elseif [M1. gg]>=15 and [M1. gg]<30 then "15<=gg<30"
elseif [M1. gg]>=30 and [M1. gg]<90 then "30<=gg<90"
elseif [M1. gg]>=90 then "gg>90"
Bands_nalmai_10.3.twbx 14.2 KB
first of all thanks for your answer.
Unfortunately it's not what I need... I need a graph over time.
So I need to know the day X how may work orders was in every band.
The Date axis should be indipendent by the "created date" and "closing date".
For example if I create a WO the 01/01/2017 and I closed the 12/04/2017 I want to know that:
- the 15/12/2016 this WO was in the band of delay : NULL (because is not yet open)
- the 31/12/2016 this WO was in the band of delay : NULL (because is not yet open)
- the 01/01/2017 this WO was in the band of delay : gg<3
- the 02/01/2017 this WO was in the band of delay : gg<3
- the 03/01/2017 this WO was in the band of delay : gg<3
- the 04/01/2017 this WO was in the band of delay : 3<=gg<7
- the 05/01/2017 this WO was in the band of delay : 3<=gg<7
- the 06/01/2017 this WO was in the band of delay : 3<=gg<7
- the 07/01/2017 this WO was in the band of delay : 3<=gg<7
- the 08/01/2017 this WO was in the band of delay : 7<=gg<15
- the 16/01/2017 this WO was in the band of delay : 15<=gg<30
- the 25/01/2017 this WO was in the band of delay : 15<=gg<30
- the 30/01/2017 this WO was in the band of delay : 15<=gg<30
- the 02/02/2017 this WO was in the band of delay : 30<=gg<90
- the 30/02/2017 this WO was in the band of delay : 30<=gg<90
- the 10/04/2017 this WO was in the band of delay : gg>90
- the 11/04/2017 this WO was in the band of delay : gg>90
- the 12/04/2017 this WO was in the band of delay : NULL (because is closed)
This concept applied to all the service order and resumed in a graph like the first one with the scale of yellow-red.
Maybe I'm not able to explain myself and I think I'm not able to explain better than my last example.
A Word order with
- created date = 01/01/2017
- closed date = 12/04/2017
you categorize it as gg>90 because it has been in OPEN status for 101 days. But this is not what I need.
In your graph you are saying: "The number of work orders opened in date X for every maximum band of delay is Y"
I need instead: "In date X the number of work orders in opened status for every band of delay at that time was Y"
The same work order, in time, change his own band of delay...
It is very different because in my point of view it is variable in time.
With the "dummy-dataset" you defined, the following graph should be the result I need.
As you can see at the first day it's not possible that there are WO in category different to 0<gg<3 because... they just have been opened.
I hope that this explain somehow what I need.
Took me some while to get it clear;) but I think a "got it":)
The approach is based on "scaffolding" and ability to use one source (excel) with to sheets and open it based on Legacy Connection
Define the join
Drag the required objects to the indicated locations. Measure M1. gg and dimension D1. Bands defined as described above and filtered date
Bands_scaffold_nalmai_10.3.twbx 27.6 KB
Sorry but the Join legacy (with <= and >=) works only if the sheets are in the same files? Correct?
1 of 1 people found this helpful
Yep, let me know if it's approach is applicable in your scenario otherwise we will continue the "search";)
I'll try to apply it because my data source is an automatic extraction so to have in the same file I have to do something and right now I don't know why.
In any case I have to better check your solution because I think that the result is not what I expect but I'll try to check in your workbook.
Thank you by the way
1 of 1 people found this helpful
Would like to ask Shinichiro Murakami to the table. Shinichiro Murakami, what are the options left when we have to stick to "autmatic extraction" without "manipulation" of the underlying data-structure/source. Hope to hear from you. Again your valuable input it appreciated
I hope I understand you request.
In this case, each WO need to have all the possible "Date" to judge the status at each "Date".
But note that if your date range becomes wide, the number of data becomes really big.
Prepare simple date table in excel or whatever, which should cover all the date range you want to show.
Assuming you have 10.2 or newer.
Copy this date table and paste as data connection.
Use Join calculation and put "1" for each.
Date_status_SM_10.3.twbx 38.9 KB
I start to give you feedback because it is helpful (i didn't know that I could paste a table as connection). Tomorrow I'll read your answer. For now, thank you
Thank you very much Shinichiro, that is exactly what i needed.
Cna I ask you also the reason why (and the theory) you use 1 = 1 with Inner Join?
I've never used this approach and of course I have to try again this method to fully understand the reasons.
Thanks also to Norbert that address to you and follow me at the beginning.
I just miss a think in my view but I think I can fix on my own!!
Thanks again all of you!!