topic Re: Need help in QlikView

Need help

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Need help

swuehl — Sun, 14 May 2017 09:21:07 GMT

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

Re: Need help

Mon, 15 May 2017 05:57:36 GMT

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

Re: Need help

Anil_Babu_Samineni — Mon, 15 May 2017 06:02:31 GMT

May be this?

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

Re: Need help

Mon, 15 May 2017 06:41:57 GMT

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

Re: Need help

swuehl — Mon, 15 May 2017 06:55:02 GMT

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 ;

Re: Need help

swuehl — Mon, 15 May 2017 07:24:19 GMT

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.

Re: Need help

Mon, 15 May 2017 08:25:45 GMT

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

Re: Need help

swuehl — Mon, 15 May 2017 08:45:52 GMT

You just need to adapt the expressions.

Re: Need help

swuehl — Mon, 15 May 2017 09:45:40 GMT

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

Re: Need help

Mon, 15 May 2017 09:55:43 GMT

Sorry My Bad.... I was looking in older qvw file. Thanks