Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Need help

@rwunderlichjimhalpert

Hi

I have a table like this as below. This is just a sample 3 records

IDOrderDateFulfillementDate
14/1/20174/10/2017
25/7/20175/11/2017
35/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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

View solution in original post

10 Replies
swuehl
MVP
MVP

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

Not applicable

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Not applicable

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

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

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.

Not applicable

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

You just need to adapt the expressions.

swuehl
MVP
MVP

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

View solution in original post