12 Replies Latest reply: Apr 4, 2012 6:39 AM by k_l_a RSS

    Total in script wrong values

    k_l_a

      Hi , in this script I need to get the total TOTALGIAC

      groupped by year and month.

      The total by item is correct but the total by year and

      month gets some differences.

      Please, What is wrong?

      QV release 10.

      Manu thanks!

       

      //***********************************************************************

       

       

      tmp:
      NoConcatenate
      LOAD
      posted  , itemNo  ,  qt

       

      RESIDENT RESULT
      ORDER BY    itemNo  ,  posted   ;

       

      dataFinal:
      NOCONCATENATE load

      qt  ,
      itemNo  ,
      posted  ,

      IF (
              (DayNumberOfYear(posted)) < 7
               and week(posted) > 2
              , 1 ,    
               week(date#( posted, 'DD/MM/YYYY')))  as weekly ,
              
      month(date#(posted , 'DD/MM/YYYY')) as month,
      year(date#(posted , 'DD/MM/YYYY')) as year ,
      day( date#(posted , 'DD/MM/YYYY')) as Day ,

      IF(
      itemNo   = Previous(itemNo),
      NumSum(Peek('Gia'), qt )

      ,qt ) as Gia

      resident tmp ;


      giac:

      NOCONCATENATE
      load
      monthName(date#(posted , 'DD/MM/YYYY')) as monthYear ,
      date#(posted , 'DD/MM/YYYY') as posting ,
      month,
      year ,
      Day ,
      weekly ,
      itemNo as item ,
      qt as QTY,
      Gia as Giace
      resident dataFinal ;


      //*****************************************************

      left join load
      itemNo  as item ,
      monthName(date#(posted , 'DD/MM/YYYY')) as monthYear ,
      sum(Gia)  as TOTALGIAC

      resident  dataFinal
      Group by
      monthName(date#(posted , 'DD/MM/YYYY'))   , itemNo   ;

      //*****************************************************

      left join load

      item ,  Family
      From.....


      Left Join load

      Famili, Lines

        • Total in script wrong values
          Sebastián Pereira


          Hi,

          First try to add monthYear in dataFinal, then, the join between //*** would be:

           

          //*****************************************************

          left join load
          itemNo  as item ,
          monthYear ,
          sum(Gia)  as TOTALGIAC

          resident  dataFinal
          Group by
          monthYear  , itemNo   ;

          //*****************************************************

           

          So, why you need a table with the values (Gia) and the totals (TOTALGIAC) ???

          If you do it for get the part of the total in graphic expressions, you don't nedd this, you can do it with TOTAL qualifier.

           

          Tell me if this was use ful for you.

          • Total in script wrong values
            Sebastián Pereira

            You must join Family and Lines before grouping sum.

             

            Try it and take in mind the last suggestion.

             

            Tell me if it works!!!

              • Total in script wrong values
                k_l_a

                But I need to group also by  Family and lines? Because I tried this solution... Could you post the correct script.

                Many thanks.

                Regards

                • Total in script wrong values
                  k_l_a

                  No, it doesn't work. The total doesn't change.
                  Look at thsi really situation:


                  First this script i aggregate the value next ..

                  //***************************************************************
                  giac:

                  NOCONCATENATE
                  load
                  monthName(date#(posted , 'DD/MM/YYYY')) as monthYear ,
                  date#(posted , 'DD/MM/YYYY') as posting ,
                  text(Subfield( text(itemNo)   , '-' ,1)) as Bulk ,  // bulk
                  text(Subfield(text(itemNo)  , '-' ,2)) as pkg , // packages
                  unit ,  // unit of meausure
                  Quarters  ,
                  month,
                  year ,
                  Day ,
                  weekly ,
                  FamilyDes,  // family description
                  itemDescription, // item description
                  Family ,
                  lines ,  // line code
                  lineDesc ,  // line description
                  itemNo as item , // product
                  qt as QTY,
                  Gia as Giace

                  resident dataFinal ;

                  //*****************************************************

                  left join load
                  item ,
                  monthYear ,
                  AVG(Giace)  as  averageRemainingQty  // average
                  resident  giac
                  Group by
                  monthYear   , item   ;

                  //*****************************************************

                   

                  the "averageRemainingQty "  are the average.
                  I need to group the average by month and by year.
                  The problem is thath
                  the average family and Lines by month and by year
                  gets some difference.

                  wy ?? ????? ??????????

                  If I calulate the average manually:
                  month= 01 year 2011 family= AAA
                  day Giace
                  01  100
                  02   450
                  03  500
                  ...
                  in excel:
                  100+450+500 .... /31 = example 2000

                  in QlikView:
                  sum(distinct(Giace))  .. for example.. 2200
                  difference  = 200

                  Some help?
                  Regards,
                  K.