Do not sum if the KEY was previously considered

[brunolelli87]

Hello guys,

I'm using a bar chart with dates (DASI field) on X axis and the sum of seats offered (TOTS field) per airline on Y axis.

So, my dimensions are: DASI and NCIA, as you can see below:

And my expression is: sum({<DASI=>} aggr(sum({<DASI=>} TOTS), KEYI)),

Where:
DASI is a date field,
TOTS is the total number of seats
KEYI is the key field.

Basically, each fligh has its own Key.

But, my problem is, once I'm summing the total of seats per KEYI and if the airplane took-off at night and landed on the other day, the system is considering two times this KEYI. Because it's a distinct KEYI on day 1 and a distinct KEYI on day 2.

How can I just consider the KEY (KEYI field) if it has not been previously considered?
So, if the airplane took-off in one day and landed on another day, I would like to consider the seats offered just on the first date.

Thanks

[Gysbert_Wassenaar]

I think the only way to sort this out is to create a field like TakeOffDate in the load script (if you don't have it already) and use that field as dimension instead of the date field you're now using.

[brunolelli87]

Hello,

Thanks for your kindly support.

I don't have a TAKEOFF DATE field because basically my database contains all aircraft position per minute. So, while the aircraft is flying she is reporting her position... Therefore, I tried to create a left join in order to obtain the first date that the aircraft reported her position.

This is my script:

Voos:
LOAD 
Date#(Date(@2, 'DD/MM/YY'),'DD/MM/YY') & @6 as KEYP,						
@1 as KEYI,																				
Date#(Date(@2, 'DD/MM/YY'),'DD/MM/YY') as DASI,
Time#(Time(@3, 'hh:mm'), 'hh:mm') as HHSI,														 	
timestamp(date(Date#(Date(@2, 'DD/MM/YY'),'DD/MM/YY') &' '& Time(@3)),'DD/MM/YYYY hh:mm')  as TIMS,    
If(@6='TAM', 'LAN',if(@6='PAM', 'PTB',@6)) as NCIA													 	
FROM
[C:\Users\bruno\Desktop\Voos\DB\Voos\Voos*.txt]
(txt, codepage is 28591, no labels, delimiter is ';', msq);

This is what I did:

Left Join(Voos)
LOAD
	KEYI, 
	Date(Min(DASI)) as min_DASI
Resident Voos
GROUP BY KEYI;

But, I don't know why it's still nor working.

Any other idea?