0 Replies Latest reply: Nov 8, 2016 7:46 AM by Buehler Daniel RSS

    Problem to calculate the days

    Buehler Daniel

      Hello i am new in Qlikview and i want do calculate the days (OPTTAG).

       

      The Problem:

      as you can see i try to calculate the OPTTAGE.

      For example when i make a sum, then i should get for Bern Tisch 1 for the first Jan 0,5 and not 1,5.

      I get the total form all the Fallnummern, but i dont need this.

      below you see the script, i tell them if the SNZ is biger than 0.1562500000, then he should count 1 day, if the SNZ is lower than 0.1562500000 then count 0,5 days.

      I need the SNZ for the OPSaal not for the Fallnumer.

       

      What do i have to write, to get the sum for the OPSaal per day and not for the Fallnummer.

       

      Please i need some help.

       

       

       

      TMPTermine:

      LOAD Fallnummer,

           OPSaal,

           Schnitt,

           Naht,

           Timestamp#(Naht,'hh:mm')                                 as NahtZeit,

           Timestamp#(Schnitt,'hh:mm')                            as SchnittZeit,

           right(Timestamp(Schnitt),8)                            as Schnittzeit,

           right(Timestamp(Naht),8)                                as Nahtzeit,

           interval(Naht-Schnitt,'hh:mm')                         as SNZ

          

      FROm

      [C:\Users\Daniel\Desktop\SNZ.xlsx]

      (ooxml, embedded labels, table is Tabelle1);

       

      Termine:

      Load

      Fallnummer,

      OPSaal,

      Schnitt,

      year(Schnitt)                                        as Jahr,

      Month(Schnitt)                                        as Monat,

      day(Schnitt)                                        as Tag,

      Schnittzeit,

      Naht,

      Nahtzeit,                                       

      sum(SNZ)                                                    as #SNZ,

      if(sum((SNZ))>='0.1562500000',1,0.5)                        as OPTTag

       

       

      Resident TMPTermine

      Group by

      Fallnummer,

      OPSaal,

      Schnitt,

      year(Schnitt),                                       

      Month(Schnitt),                                       

      day(Schnitt),

      Schnittzeit,

      Naht,

      Nahtzeit;

       

      Drop Table TMPTermine;