sumifs with timestamps, in script

Wed, 17 Feb 2016 15:10:20 GMT

Hi All,

I have browsed through a lot of posts about sum if scripting/set analysis, also tried some of the answers given, though I cannot make my specific case work, therefore I am posting my question.

I have multiple data sets (see underneath) which give timestamps of train trips. locomotive number and KwH usage and I need to have the sum of KwH usage per TRAINRUN_ID. Now there are two key variables/conditions: Loco and Timestamps arrival - Timestamps departure. I need to divide the KwH usage based on these conditions -> = Loco Number and that the DateTime is in between the above timestamps.

Min_Max_Table:

Load

TRAINRUN_ID,

Min_Timestamp_Departure,

Max_Timestamp_Arrival

Resident

EBIS_TRS

GROUP BY

TRAINRUN_ID;

right join(EBIS_TRSB)

LOC:

load TRSB_ID, (TRSB is eventually coupled with a TRAIN_ID like in the previous table)

LOC_NO

Electricity:

Load

LOC,

DateTime,

KwH_Usage_Postive,

FROM ..

You need to divide based on the LOC number because that number is coupled with a TRAINRUN_ID. I tried to do something like:

sum(If(DateTime>=Min_Timestamp_Departure and DateTime<=Max_Timestamp_Arrival and LOC = LOC_NO , KwH_Usage_Postive , 0)

Obviously this did not work, maybe I have to use something else than a sumifs formula, please help me .

Re: sumifs with timestamps, in script

jonathandienst — Thu, 18 Feb 2016 07:37:08 GMT

I suggest that you upload a sample qvw containing some representative data.

topic sumifs with timestamps, in script in QlikView

sumifs with timestamps, in script

Re: sumifs with timestamps, in script