Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I've been messing with this for 7+ days and I think I'm close, but could use a boost over the fence please:
Here are the details:
1 table: id, created_when, validated_when + various other fields.
master calendar/island.
OBJECTIVE:
Combo chart with 3 elements:
Date as dimension
Line 1: Items created that day
Line 2: Items closed that day
...so far so good. I created some flags, works fine.
Bars across the bottom, I'd like to show total OPEN(outstanding) items on that day.
Suffice to say, if an item is created on 2/1, closed on 2/10, then from 2/1-2/09 it would be among the counted 'open'.
OpenValidatedDate:
Load
id,
id as OVID,
created_when as CreatedDate,
created_when as Date,
1 as Flag_Created,
Resident rfi;
Concatenate (OpenValidatedDate)
Load
id,
id as OVID,
validated_when as ValidatedDate,
validated_when as Date,
1 as Flag_ValidatedDate
Resident rfi
where exists (validated_when);
IntervalMatch:
IntervalMatch (Date)
Left join
Load distinct CreatedDate, ValidatedDate
resident OpenValidatedDate;
Maybe I have looked at the wrong charts, but I can't see that you are using ReferenceDate as dimension in you combo chart. The solution I suggested will not create the correct open count if you are using a different date field that is only linked by ID to the DateLink table.
I see that you also want to show the sum of created and validated IDs in your chart. To be able to do this easily, create flags for this in the DateLink table:
DateRange:
LOAD ID, created, If(Len(Trim(validated)),validated,Today()) as validated;
LOAD ID,
SetDateYear(Date#(created,'M/D'),2016) as created,
SetDateYear(Date#(validated,'M/D'),2016) as validated
INLINE [
ID, created, validated
1, 2/1, 2/5
2, 2/3, 2/7
3, 2/3,
4, 2/5, 2/6
5, 2/6,
];
LinkTable:
LOAD ID, Date(created+iterno()-1) as ReferenceDate,
If(iterno()=1, 1,0) as CreatedFlag,
If(Date(created+iterno()-1) = validated, 1,0) as ValidatedFlag
Resident DateRange
WHILE created+iterno()-1 <= validated;
Then link your master calendar to the ReferenceDate, use ReferenceDate as dimension and as expressions
=Count(DISTINCT ID)
=Sum(CreatedFlag)
=Sum(ValidatedFlag)
Hi Stefan,
Ok, so I took your suggestions. Please see updated qvf.
If you'll look at the Sandbox2 tab, it will show you that it's close, but
not quite accurate. I advise you to choose 'Port in SmartEdge & N&SIS
Check' as a 'Rule Name' as it narrows the data substantially.
If you'll noticed, there are no validated dates until 2/19, so the count
should be incrementing for each created item on dates prior, and it's not
consistently. I'm not sure what it's doing.
I did make a change to the master calendar as referenced in 'Main'
(TempDate as ReferenceDate,)
Thoughts?
Thanks again.
I currently can't see an anomaly. I count 9 created until 2/19 and the open id count shows 9 on that date.
Also the dates before seem ok to me. Could you point me specific to a date or ID that is not correct?
edit: note that our current script code creates a ReferenceDate for all dates between created and validated, including these dates.
If you don't want this, we can change it.
I'm not sure what I was seeing - you are correct. So if you select the Rule name 'Description SOEM vs Smartedge' it lays it out pretty well. 40 (all total) created on 2/10, then slowly starts validating them.
What I do notice is that it shows the validation happens the day after - ie, if it validates on 2/14, it will show the reduction on 2/15. This is an acceptable issue but if you know a quick way to solve that, theoretically the validation should reduce the total on the same day it's validated.
As far as the dates go, I think we'll need the ability to control the dates a bit more - would we need a separate master calendar to link to all possible dates or? I notice that my ranges are limited in some instances (due to no created/validated dates before/after certain days), and the user will prefer to limit their date dimension view themselves.
I can't thank you enough for all your help. This has been a godsend.
Ok, that's good that my eyes still working
For your open count to exclude the validation date, maybe create an explicite open Flag:
LOAD ID, Date(created+iterno()-1) as ReferenceDate,
If(iterno()=1, 1,0) as CreatedFlag,
If(Date(created+iterno()-1) = validated, 1,0) as ValidatedFlag,
If(created+iterno()-1 < validated,1,0) as OpenFlag
Resident DateRange
WHILE created+iterno()-1 <= validated;
Then Sum(OpenFlag) instead doing a Count(DISTINCT ID)
And yes, I think you should create a master calendar for ReferenceDate, I thought you've already done this, how have you created it?
Hi Stefan,
Jury's still out on the other calendar. I still don't quite understand how to integrate them or if I even need another one. Another concept therein is the canonical date which I've played with, but is currently commented out. Again, struggling with concept a bit there.
Based on your last post, the graph is working nearly perfectly, thank you! For some reason, 'today', Cleared(validated) and Open swap for some reason. Strange behavior, and only on the current day (there is no actual data past 5/11 in this dataset). Would you know why?
DateRange:
LOAD
id,
created_when as createddate,
If(Len(Trim(validated_when)), validated_when, Today()) as validateddate
resident rfi;
LinkTable:
LOAD
id,
Date(createddate+iterno()-1) as ReferenceDate,
If(iterno()=1, 1,0) as Flag_Created,
If(Date(createddate+iterno()-1) = validateddate, 1,0) as Flag_Validated,
If(createddate+iterno()-1 < validateddate,1,0) as Flag_Open
Resident DateRange
WHILE createddate+iterno()-1 <= validateddate;
Yes, that's due to the way that we create Today() as validateddate for every ID without a validateddate in the source data, than later on, we create the Flag_Validated for that date (though it should be still open logic wise).
Maybe we can fix this by setting the validateddate for these ID to 1 day in the future, then filter the reference dates up to today:
DateRange:
LOAD
id,
created_when as createddate,
If(Len(Trim(validated_when)), validated_when, Today()+1) as validateddate
resident rfi;
LinkTable:
LOAD *
WHERE ReferenceDate <= Today();
LOAD
id,
Date(createddate+iterno()-1) as ReferenceDate,
If(iterno()=1, 1,0) as Flag_Created,
If(Date(createddate+iterno()-1) = validateddate, 1,0) as Flag_Validated,
If(createddate+iterno()-1 < validateddate,1,0) as Flag_Open
Resident DateRange
WHILE createddate+iterno()-1 <= validateddate;
Looks like that did the trick. The Today()+1 makes sense, though I wouldn't have figured out the Load * where ReferenceDate <= Today(). Basically it's telling it to load all (only) data from yesterday and before, correct? I would have put that statement after we set the ReferenceDate name. I still have a lot to learn.
Thanks again for all your amazing help. I think we're good
Cheers,
Joey