topic Re: Seperate Interval Sums in App Development

Seperate Interval Sums

KarimBen — Thu, 13 Jan 2022 09:07:26 GMT

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#M72519

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

Re: Seperate Interval Sums

MarcoWedel — Thu, 13 Jan 2022 17:54:21 GMT

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

Re: Seperate Interval Sums

KarimBen — Fri, 14 Jan 2022 11:41:42 GMT

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

Re: Seperate Interval Sums

MarcoWedel — Sat, 15 Jan 2022 17:32:34 GMT

Hi,

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

hope this helps

Marco

Re: Seperate Interval Sums

KarimBen — Mon, 17 Jan 2022 09:00:20 GMT

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

Re: Seperate Interval Sums

MarcoWedel — Mon, 17 Jan 2022 11:19:42 GMT

you're welcome

glad it helped