topic Min date in each day in QlikView

Min date in each day

Tue, 22 Mar 2011 14:52:41 GMT

Hi,

I have a table with driving details that includes the date, car key, customer key and the meter reading at the beginning and end of the drive.

I want to show in a chart the car key and the average of the start meter reading of the first drive of every day.

For example if I have the following information:

01/01/2011 08:30, car1, cust1, 100, 150

01/01/2011 16:50, car1, cust1, 150, 270

01/02/2011 08:30, car2, cust2, 50, 130

01/02/2011 09:00, car1, cust1, 270, 320

01/02/2011 18:00, car1, cust1, 320, 390

01/02/2011 19:00, car2, cust2, 130, 170

The result will be:

Car1 -> 185 - (100+270)\2

Car2 -> 50

The average will be calculated according to the bold lines.

Any suggestions?

Thanks,

Hadar

Min date in each day

Tue, 22 Mar 2011 15:14:02 GMT

To be honest I don't really understand what you are trying to do... where does the 185 come from? It looks like you either want a flag for when it is the first drive of the day for that car or you could use some form of total <some field> but... I'm not really sure what you're trying to do so I can't say.

Min date in each day

Tue, 22 Mar 2011 15:23:45 GMT

I need to look at the first drive in each day for each car and calculate the average of the start meter reading.

Following the example I gave before, for Car1 I will look at the first drive in 01/01/2011 (which happened at 08:30) ->the meter reading is 100, and the first drive in 01/02/2011 (which happened at 09:00) ->the meter reading is 270, the average of 100 and 270 is 185,

Does that make more sense?

Min date in each day

Oleg_Troyansky — Tue, 22 Mar 2011 15:34:19 GMT

Hadar,

you need to use Advanced Aggregation (function AGGR()):

- First, you need to determine the first drive of the day by using FirstSortedValue(), aggregated for each Car and Date

- Then, you need to average out the results.

the formula should be something like this:

AVG( AGGR( FirstSortedValue( Meter, Timestamp) , CarID, Date) )

That's assuming that you have separate fields for Timestamp, Date, etc...

Min date in each day

Tue, 22 Mar 2011 15:45:26 GMT

Oleg,

Thanks you! It worked!