Need help

[#1]

@rwunderlich‌jimhalpert‌

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

[swuehl]

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).

[#3]

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

[Anil_Babu_Samineni]

May be this?

RangeSum(Count({$<Flag={'Logged'}>} DISTINCT ID) - Count({$<Flag={'Closed'}>} DISTINCT ID ),0,RowNo())

[#5]

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

[swuehl]

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 ;

[swuehl]

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.

[#8]

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

[swuehl]

You just need to adapt the expressions.

[swuehl · Accepted solution]

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