Using the Min Function to Get the Oldest Record by Date

building semantic layer
1

I am developing a dashboard chart that shows tickets at a call center and the accuracy rate of completion. In order to capture the accuracy we base the rate on whether the ticket was reopenned due to the call center making an error. However, tickets can be re-openned several times, and also assigned to different call reps if one is absent or unavailable to fix an error. So in order to capture a more accurate rate we need to break the rate into two catergories… 1) what is the rate of submitted tickets that were reoppenned due to our error. 2) of the reoppenned tickets what is the accuracy rate of completion. We cant base number 1 on all ofthe reopenns against the total tickets becuase they are two sepearte things. one ticket can be reoppened numerous times and it could be for another reps error so we don’t want to dock the original rep for that. I know thats a long story but was trying to give some background…

In teh universe i want to create a filter that says only give me the the reopenned reocrd for tickets the very first time it was openned. In other words the min-- reopen date record. This is what i have tried and it does not parse… @Select(Reopen folder\Lastclosedate) = min(@Select(Reopened Folder\Lastclosedate))

A suggestion was to use…

Try to use nested Select

@(Field1) = (Select Min(@(Field1) from tablename Where )

But i am confused by that becuase, partially … i don’t know why you would use @ without @ “select” and then why do we need the table name, also confused about the join suggestion.

Any help would be greatly appreciated!

Thanks.
JoBeth[/quote]

joheim (BOB member since 2010-02-12)

2

Hi JoBeth,

You need something like this:


where table1.last_close_date = (select min(table2.last_close_date) from table table2 where table2.ID = table1.ID)

Yes, you are right about the @ syntax. You said:

You would need an @Select in the appropriate places in the statement.

Hope that helps,
Judy

JMulders :us: (BOB member since 2002-06-20)

3

so what if there is not join condition. How would i write it then just…

select min(par.dbo.par_reopen.reopenclosedate)

???

joheim (BOB member since 2010-02-12)

4

You need to join table1 and table2 to correlate the ID part. It means you need a oldest last_close_date for each ID.

You need to define it as a pre-defined condition in Universe and then use it in the report:

table1.last_close_date = (select min(table2.last_close_date) 
                                from table table2 
                                where table2.ID = table1.ID)

It will return oldest last_close_date for each ID. Give that a try…

BO_Chief :us: (BOB member since 2004-06-06)

5

Thanks… it parsed ok, so thats a step in the right direction… now just have so see if it works in the query.

Thanks for your help!

joheim (BOB member since 2010-02-12)