Total in script wrong values

[#1]

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

[sebastiandperei]

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.

[#3]

the Gia values is the remaining qty and the Sum(Gia) is the Total gia by month and

year.

The problem is the total in family dimensin and  in the lines dimension.

[sebastiandperei]

You must join Family and Lines before grouping sum.

Try it and take in mind the last suggestion.

Tell me if it works!!!

[#5]

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

Many thanks.

Regards

[#6]

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.

[sebastiandperei]

In first place, excuse my english level, im from Argentina.

In the script you wrote:

Left Join Load

     Item,

     MonthYear,

     AVG (Giace) as averageRemainingQty

Resident giac

Group by

     MonthYear,

     Item;

Qlikview will calculate average of Giace, grouping all values (even repeated) of MonthYear and Item.

When you write Sum(Distinct(Giace)), if there is some Month and Items with the same value, QV will only get one occurrence, and the average will affected.

Joining the grouping aggregation you will repeat these values for all other registers. Please, check replacing the "Left Join Load" sentence with:

giac_average:

Load Distinct

     item,

     monthYear,

     AVG (Giace) as averageRemainingQty

Resident giac

Group by

     monthYear,

     item;

This will generate another table with the averages, so any combination will get only one value of Avg(Giac). Take in mind that also will create sinthetic keys, but we can resolve it later...

Tell me what it gives you, or reduce document and post it.

[#8]

Thanks, but doesn't work, .

[sebastiandperei]

I know it wont work, i want to know that it gives you...

[#10]

The same values I posted  in the post before. Nothing changes.