Calculate Age and Filter Fields based on Selected Date.

[#1]

HiEveryone,

I need tocreate a graph and pivot table that shows the amount of open tickets over arange of dates. For example, say a user selects a range of Dec. 20^th2011 through Jan. 8^th 2012. The graph and pivot table will show foreach day the amount of tickets that were open, separated by how long they havebeen open on each day.  I have attached avisual example.

Basically Ineed to calculate the age of each ticket based on the selected range.  In my data I have the creation date for eachticket, I need to subtract that from each of the user selected dates to getthat ticket’s age on each selected day.

So if I select a different range, the chart below recalculates based on the new selected dates.

Is that possible in QlikView?

[Miguel_Angel_Baeyens]

Hi Marek,

Yes, that is possible. But my guess is that you need more than one date field (or value) to get the age of each ticket, or in other words, ho do you know a ticket is less than 1 week old based on the CreationDate? Is that the difference between CreationDate and Today()? Or is it the CreationDate minus the upper date selected? So for example, the following expression will return the count of open tickets older less than one week

Count({< TicketID = {"=Date(CreationDate) - Today() <= 7"} >} TicketID)

Using an expression in the set analysis to return all values in field TicketID where CreationDate - Today is less than or equal to 7. But I'm not sure about the calculation of the difference.

Hope that gives you the idea.

Miguel

[#3]

Hi Miguel,

This is sorta what I need:

for each tmpDate in SelectedDateRange

     CreationDate - tmpDate = Age;

Next

So if I select 5 days, I need to see the age for each of the selected 5 days.

Does that make sense?

[Miguel_Angel_Baeyens]

Hi,

Sorry, but not quite. Do you mean you want to get it in the script? If not, expressions get more complex as far as I see, because the "Today()" in my expression above should be changed for each of the possible dates in the range. I'm likely missing something that and I can figure it out if you post some sample data we can use.

Miguel

[johnpaul]

How is the user selecting the date range?

Are you using set analysis?

The for each / next concept isn't the solution to this problem.

I would go about it as follows:

use a couple of date variable to define the start and end date of the selection.

NumberOpenedThisWeek = count({$< Ticket_Date_Opened = {">=$(vDateStartWeek1)<=$(vDateEndWeek1)"}>}Ticket_Date_Opened)

Using the "=Date(CreationDate) - Today() <= 7" like Miguel suggested will only show you tickets which were opened in the last week. I suspect that you are trying to produce a true point in time report.

[#6]

Hi Everyone,

So this was my solution.  To answer you Miguel, yes, Today() needed to change for each of the dates in the range. That range would be determined from a sider object on the report.

On that, this is how I solved the solution. During the load, I created a table that calculcated the values on each day new data is run. I then take this data, and append it to a historical table which I load once the new data is processed. This way I capture each days value in my historical data table without the need to calculate it on the fly on the report page, which is what I was trying to do initially with my question.

Thanks for everyones help!