topic Total in script wrong values in QlikView

Total in script wrong values

Fri, 23 Mar 2012 13:16:55 GMT

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

sebastiandperei — Fri, 23 Mar 2012 13:30:01 GMT

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

Fri, 23 Mar 2012 13:37:53 GMT

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.

Total in script wrong values

sebastiandperei — Fri, 23 Mar 2012 14:28:06 GMT

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

Sun, 25 Mar 2012 10:50:30 GMT

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

Mon, 26 Mar 2012 12:25:00 GMT

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.

Total in script wrong values

sebastiandperei — Mon, 26 Mar 2012 15:35:30 GMT

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.

Total in script wrong values

Tue, 27 Mar 2012 08:30:04 GMT

Thanks, but doesn't work, .

Total in script wrong values

sebastiandperei — Tue, 27 Mar 2012 19:45:00 GMT

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

Total in script wrong values

Thu, 29 Mar 2012 06:23:18 GMT

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

Total in script wrong values

Fri, 30 Mar 2012 09:32:16 GMT

QV 10 is very dangerous many many files doesn't work. WE have many problem with this release.

Total in script wrong values

sebastiandperei — Fri, 30 Mar 2012 13:32:39 GMT

Could you send the qvw?

I never had problems with QV10... What kind of problems do you have?

Total in script wrong values

Wed, 04 Apr 2012 10:39:05 GMT

No I can't.. . The data are not of mine. I posted the code. above