Forum Discussion

shellyvanerp's avatar
shellyvanerp
Frequent Visitor
6 years ago

Summarize time data into new table

Hi,    I have a table with car trips, looking like this:           StartDate                      StAddress   EndDate                      EndAddress  Distance  Duration  SameTrip  1     15/1/20...
shellyvanerp's avatar
shellyvanerp
Frequent Visitor
6 years ago

I just now found a way to get a new index column that gives the same index to rows that belong to the same trip. But it is not pretty, so if anyone has a better solution I am very happy to hear. 

 

NewIndex = SWITCH(
TRUE();
-- window 1
CapBusinessTrips[SameTrip] <> BLANK() && CALCULATE(MAX(CapBusinessTrips[SameTrip]);FILTER(CapBusinessTrips; CapBusinessTrips[Index]=EARLIER(CapBusinessTrips[Index])-1)) <> BLANK() && CALCULATE(MAX(CapBusinessTrips[SameTrip]); FILTER(CapBusinessTrips; CapBusinessTrips[Index]=EARLIER(CapBusinessTrips[Index])-2)) <> BLANK() && CALCULATE(MAX(CapBusinessTrips[SameTrip]);FILTER(CapBusinessTrips; CapBusinessTrips[Index]=EARLIER(CapBusinessTrips[Index])-3)) = BLANK(); CALCULATE(MAX(CapBusinessTrips[Index]);FILTER(CapBusinessTrips; CapBusinessTrips[Index]=EARLIER(CapBusinessTrips[Index])-3));
 
-- window 2
CapBusinessTrips[SameTrip] <> BLANK() && CALCULATE(MAX(CapBusinessTrips[SameTrip]);FILTER(CapBusinessTrips; CapBusinessTrips[Index]=EARLIER(CapBusinessTrips[Index])-1)) <> BLANK() && CALCULATE(MAX(CapBusinessTrips[SameTrip]);FILTER(CapBusinessTrips; CapBusinessTrips[Index]=EARLIER(CapBusinessTrips[Index])-2)) = BLANK(); CALCULATE(MAX(CapBusinessTrips[Index]);FILTER(CapBusinessTrips; CapBusinessTrips[Index]=EARLIER(CapBusinessTrips[Index])-2));
 
-- window 3
CapBusinessTrips[SameTrip] <> BLANK() && CALCULATE(MAX(CapBusinessTrips[SameTrip]);FILTER(CapBusinessTrips; CapBusinessTrips[Index]=EARLIER(CapBusinessTrips[Index])-1)) = BLANK(); CALCULATE(MAX(CapBusinessTrips[Index]);FILTER(CapBusinessTrips; CapBusinessTrips[Index]=EARLIER(CapBusinessTrips[Index])-1));
 
-- else
CapBusinessTrips[Index])