Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have a table like this as below. This is just a sample 3 records
ID | OrderDate | FulfillementDate |
1 | 4/1/2017 | 4/10/2017 |
2 | 5/7/2017 | 5/11/2017 |
3 | 5/13/2017 |
Using the above we have created a Master Calendar from 4/1/2017 to 5/13/2017 and linked these tables.
My requirement is to find out Age and Age bucket of each ID until it was fulfillled . The age will be CalendarDate - OrderDate ...
Lets say we have couple of Age Bucket 0-4 Days and > 4 Days so ID 1 was in 0-4 Days bucket as 8th April 2017 but it was shifted to >4 Day.i want to show this in trendchart as we want to know how many IDs where in these age buckets as on that calendar date
Shouldn't this equal to the 'Open' tickets?
Anyway, if you want to discuss 'wrong' results, you need to define your expected results, in numbers and in verbal definitions if the numbers don't speak for themselves...
Have a look at
Creating Reference Dates for Intervals
you can calculate the age values and the buckets in the reference date link table, as shown by Henric (instead of age() function, just calculate the difference of the dates if you need age in days).
rwunderlich I have attached the Data and qvw in original post but still not getting the desired result could you please check once where am wrong. for example ID 1 should be in 0-3 bucket as of 22nd April but am not getting it.
Thanks for the support
May be this?
RangeSum(Count({$<Flag={'Logged'}>} DISTINCT ID) - Count({$<Flag={'Closed'}>} DISTINCT ID ),0,RowNo())
No This does not give right answer .. for example it gives 25th april 0-3 days as 3 tickets but there is only 1 in that bucket which is ID 2
You are creating reference dates for all IDs until today, you need to limit to the ClosedDate:
Reference_Table:
Load * ,
if(Age<=3,'0-3','>3') as [Age Bucket];
Load ID,
Interval#((SubmitDate + IterNo() -1) - SubmitDate,'dd') as Age,
Date( SubmitDate + IterNo() -1) as ReferenceDate
Resident TicketTemp
While IterNo() <= Rangemin(today(),ClosedDate) - SubmitDate + 1 ;
Another suggestion:
Create the logged and closed flags in the reference date table (not in the ticket table), where reference date equals resp. date.
Then it's easy to create the statistics per reference date, filter on closed / logged flagged reference dates or just do a plain count for open tickets.
There are examples here in the Forum that demonstrates that.
I have made changes as per your suggestion in the attached qvw We are not getting values for missing dates in chart and desired output
for example not getting data from 22nd to 24th April which we have created as missing date in Master calendar and reference table
You just need to adapt the expressions.
Shouldn't this equal to the 'Open' tickets?
Anyway, if you want to discuss 'wrong' results, you need to define your expected results, in numbers and in verbal definitions if the numbers don't speak for themselves...