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.