Need something like analytic functions in Oracle

[#1]

Hello,

I'm a begginer in QV developing and I really need your help. Please read following description, I'd like to find out if it is possible.

I have table with users tickets and table with tickets logs

ticket_logs has following fields:

ticket_id, log_id, state_id, log_date

Ticket states can be: created, assigned, active and closed

I need to make a chart which will show for week dimention how many tickets were not in state 'closed'

So I need calculate for each week the last state of each ticket in this week. I easily can do this in Oracle. and I have no idea how manage it in QV. Please, help me, any advice, something.

[Gysbert_Wassenaar]

You can use the FirstSortedValue function for this. If you use ticket_id as chart dimension then the expression FirstSortedValue(state_id, -log_date) will return the last state_id value for each ticket_id.

For a count of tickets per week you need a week dimension. You can create this in the script using something like WeekStart(log_date) as Week. You can then use this expression in your chart: count({<state_id -= {'closed'} >}distinct ticket_id). The expression editor does not understand -=, but the syntax is correct. See this blog post for more information: Excluding values in Set Analysis

[#3]

Thank you, Gysbert!

Unfortunately, seems it will not help me. Look, we have two tickets

So, we see that ticket2 (ticket_id = 2)  was created in week W42, in week W43 it wasn't touched by anyone, but it was alive, due to it should be counted in "not closed", but using count({<state_id -= {'closed'} >}distinct ticket_id)

I will receive only that ticket1 wasn't closed in week W43, but they were both not closed.

Hope, You or someone else will help me, Ihad to count such stuff, really. Thank you!