Seperate Interval Sums

[KarimBen]

Hi everyone, 
thanks to @MarcoWedel  and @Taoufiq_Zarra , i was able to solve the following task perfectly:     https://community.qlik.com/t5/App-Development/Sum-of-Productive-Runtime-of-Maschines/m-p/1879818#M72...

However, the summaries go over several days. But I have to make precise evalations for the day. 

[MarcoWedel]

Hi,

if there are intervals spanning across multiple days, you have to split these intervals in daily sub-intervals to be able to aggregate on day level.

Extending my example from https://community.qlik.com/t5/App-Development/Sum-of-Productive-Runtime-of-Maschines/m-p/1879818 this could be done like this:

tabMaschinenLaufzeitenTemp:
LOAD rowid as %rowid,
     Timestamp#(Replace(Left(timestamp,26),'T',' '),'YYYY-MM-DD hh:mm:ss.fffffff') as DatumZeit,
     timestamp,
     tier1 						as Grün,
     tier2						as Gelb,
     tier3						as Rot,
     If(tier1=0 and tier2=0 and tier3=0,1) as Counter,
    
     If( tier1=1, 'läuft',
      If(tier1=0 and tier2=0 and tier3=0,'nicht produktiv',
      If(tier1=2, 'Schacht i.O. voll',
      If(tier2=1, 'Einrichten',
      If(tier2=2, 'Schacht nicht erfasst voll',
      If(tier3=1, 'Störung',
      If(tier3=2, 'Schacht n.i.O. voll'    
      ))))))) as IndiCom,
      
     If(tier1=1, Dual('läuft',1), Dual('läuft nicht',0)) as Lauf,
     
     [slaveref/refid],
      Pick(Match([slaveref/macid],'00A391','00A399'),'Brune4','Brune7') as Slave,
     %Key_data_CD43325B85172CA2
FROM [lib://C_Temp/MDE_Export.xml] (XmlSimple, table is [data/slavestate]);

tabMaschinenLaufzeiten:
LOAD *,
     Interval(EndDatumZeit-StartDatumZeit) as Dauer;
LOAD *,
     Timestamp(RangeMax(DatumZeit,       DayStart(DatumZeit,IterNo()-1)),'YYYY-MM-DD hh:mm:ss.fff') as StartDatumZeit,
     Timestamp(RangeMin(NaechstesDatumZeit,DayEnd(DatumZeit,IterNo()-1)),'YYYY-MM-DD hh:mm:ss.fff') as   EndDatumZeit
While IterNo() <= Alt(DayStart(NaechstesDatumZeit)-DayStart(DatumZeit)+1,1);
LOAD *,
     If(Slave=Previous(Slave), Previous(DatumZeit)) as NaechstesDatumZeit
Resident tabMaschinenLaufzeitenTemp
Order By Slave, %rowid desc;

DROP Table tabMaschinenLaufzeitenTemp;

splitting this initial intervals

into these

thus enabling aggregations on day level:

hope this helps

Marco

[KarimBen]

Hello Marco, 
thank you very much. 
I get the table view, like yours. 
But my Barchart is giving wrong values. 

Ho do you generate your "Datum" field. 
Best regards

[MarcoWedel · Accepted solution]

Hi,

you can just use the autoCalendar Date field derived from the start timestamp (StartDatumZeit) of each sub interval:

hope this helps

Marco

[KarimBen]

Hello Marco Thank you a lot, 
Now i got it. It is perfect. 

[MarcoWedel]

you're welcome

glad it helped