# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor

## Complex Aging and Date Calculations

Hello,

I have been trying to determine what is the best avenue to take to create an app that analyzes the age and condition of customer issue tickets we have had in our system over a 6 month period.

So I have a data set with tickets that have both an open date and close date (or the close date field is empty if the ticket is still open). The list for the last 6 months includes any open items at the end of the 6 month period, all items that were closed in that six month period regardless of when they were opened, and all tickets that were created in the six month period. Basically all activity during the 6 months.

For example the date would like this table:

 Ticket Number Open Date Close Date 108120 1/12/2018 2/18/2018 108127 2/2/2018 3/30/2018 108145 2/23/2018 108178 3/16/2018 4/11/2018 108181 4/6/2018 4/9/2018 108185 4/27/2018 108211 5/18/2018 108216 6/8/2018 6/21/2018 108235 6/29/2018

What I have been trying to figure out is how to create an app that dynamically recalculates the average age of open tickets, average days to close closed tickets based on the month end date of a currently selected month. So if the user selects April the app will show the average age of all tickets that were open at 4/30/2018 even if those tickets were closed in May or June.

I have been reading and testing out set analysis in chart functions with exclusions, unions, and intersections but I have not been successful at getting this to work. Is set analysis even the way to go with these types of calculation? Should I look at somehow setting up another table with the aging information at each month end?

Is there a guide to setting up these more complicated date and age calculations somewhere?

Thank you in advance for any guidance you can give me!

Tags (2)
5 Replies
Contributor II

## Re: Complex Aging and Date Calculations

Hi Aaron,

Set analysis should work well for this, but it requires your data to be set up a certain way during the load. I am guessing you have other tables, not just the one you describe. If your set analysis tries to compare a field in one table to a field in another table, it won't work. Also, set analysis syntax is very precise; one error, and the whole thing is toast.

What set analysis have you tried so far?

Valued Contributor

## Re: Complex Aging and Date Calculations

So you can only tie one date to a "Date" filter, in your case either Open Date or Close Date, but what you really want is a status as of Date, either Opened or Closed.

You can use IntervalMatch to create a record for each day in your load script and set whether it is Open or Closed on this day. They use the newly created Status Date to filter and perform your calculations. Creating it by day rather than month end is easier IMHO and allows for more flexibility (week, month, quarter, etc).

Check out the help for IntervalMatch

https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/Scripting/ScriptPrefixes/IntervalM...

New Contributor

## Re: Complex Aging and Date Calculations

Hi Laurie,

So far I have done simple set analysis just limiting calculations or unions of fields. I knew these date calculations would be a lot more complex but didn't know where to start.

I've tried if statements with, around, or inside the set analysis but with little luck.

My period end and open close dates are in different tables. I'll look into fixing that.

New Contributor

## Re: Complex Aging and Date Calculations

Thank you David, will look into interval match. Haven't gotten into much writing of script yet.

Contributor II

## Re: Complex Aging and Date Calculations

I also recommend reading these posts:

https://community.qlik.com/blogs/qlikviewdesignblog/2015/11/02/the-as-of-table

https://community.qlik.com/docs/DOC-6593

The first one is by a Qlik veteran who writes extremely clearly. I think it gets to what you want to achieve.