topic sum total in a field in QlikView

sum total in a field

Thu, 01 Sep 2011 12:18:04 GMT

Hello,

I need to solve this case

date Qt tot

01/01/2011 10 10

02/02/2011 -5 5

03/02/2011 80 85

14/03/2011 -70 15

I need to put the total value in a field. not in a table.

Please someone could to post the script?

Thanks

regards.

sum total in a field

its_anandrjs — Thu, 01 Sep 2011 12:25:39 GMT

Hi,

As i understand i suggest you need to use Sum(Total tot) for the total value where ever you use this it is show total value of the tot.

I want to know where you want to put this value, please clear your requirements.

Regards,

Anand

Re: sum total in a field

lironbaram — Thu, 01 Sep 2011 12:26:24 GMT

hei look at this example

look at the load script

the trick is using numsum and peek functions

Re: sum total in a field

Thu, 01 Sep 2011 13:07:32 GMT

thanks but I nedd to group also by item:

date item Qt tot

01/01/2011 A 10 10

02/02/2011 A -5 5

03/02/2011 A 80 85

14/03/2011 A -70 15

01/01/2011 B 1 1

02/02/2011 B 5 6

03/02/2011 B 80 86

14/03/2011 B -70 66

Re: sum total in a field

Thu, 01 Sep 2011 14:05:21 GMT

Please,

is not possible to get total in field and groupping by Iyem and date ?

If I select Item 'A? I need to see in field every total referred to the item I selected.

best regards ,

Thanks

sum total in a field

Thu, 01 Sep 2011 23:19:58 GMT

You might be able to satisfy your requirement by defining a document variable named var_sumtot like this: =sum(tot) and then whenever you use the variable var_sumtot it will contain the sum value you are looking for.

sum total in a field

Fri, 02 Sep 2011 06:38:10 GMT

please could to post the script?

Many thanks.

best regards,

sum total in a field

Fri, 02 Sep 2011 12:33:15 GMT

I need to abtain thsi result in field:

for the "AAA" Item :
100
84
80

and for the "CCC" item:

26
22

IF I use this script it doesn't work becouse I'm not anle to
group by post and by Item. The value in total field was wrong:

inv:
load * INLINE [

post, item , QTY

01/01/2011 , AAA, 100
05/01/2011 , CCC, 26
07/01/2011 , CCC, -4
02/01/2011 , AAA ,-16
03/01/2011 , AAA, -4
];

load *,
numsum(peek('QTYTOTAL') , QTY) as QTYTOTAL
resident inv;

drop table inv ;

Please could to post the script solution for this question?
Many thanks
K.

sum total in a field

lucas4bi — Fri, 02 Sep 2011 16:20:01 GMT

Hello,

first you need to order by ITEM and Post, then you can you that script code to calculate your progressive total:

load * INLINE [

post, item , QTY

01/01/2011 , AAA, 100
05/01/2011 , CCC, 26
07/01/2011 , CCC, -4
02/01/2011 , AAA ,-16
03/01/2011 , AAA, -4
] ORDER BY item, post;

load *,
numsum(peek('QTYTOTAL') , QTY) as QTYTOTAL
resident inv;

drop table inv ;

this should work,

let me know

sum total in a field

Fri, 02 Sep 2011 17:34:24 GMT

It wprks just for a AAA item. The CCC value was wrong
Not for all.
Please chk below what I put in script:

inv:
load * INLINE [

post, item , QTY

01/01/2011 , AAA, 100
05/01/2011 , CCC, 26
07/01/2011 , CCC, -4
02/01/2011 , AAA ,-16
03/01/2011 , AAA, -4
] ;

pk:
load
post as pos, item as itm,
QTY as qq
resident inv
//GROUP BY post, item
ORDER BY item , post ;

load *,
numsum(peek('QTYTOTAL') , qq) as QTYTOTAL
resident pk ;

drop table inv , pk ;

:((((

many thks.
K.

Re: sum total in a field

Sat, 03 Sep 2011 05:28:09 GMT

I attached the wtong data result .
Please someone could have an other solution?

Thankyou
Regards,
K.

Re: sum total in a field

Jason_Michaelides — Sat, 03 Sep 2011 06:53:00 GMT

Have you tried lucas4bi's solution?

Re: sum total in a field

Sat, 03 Sep 2011 09:07:29 GMT

No , I don't.

please the solution should be with a field thath contains the progressive total .

Thanks.

Re: sum total in a field

Jason_Michaelides — Sat, 03 Sep 2011 12:18:30 GMT

Here you go - should be working now. You need to reset the Peek() on every change of Item:

Data:

LOAD * INLINE [

post, item , QTY

01/01/2011 , AAA, 100

05/01/2011 , CCC, 26

07/01/2011 , CCC, -4

02/01/2011 , AAA ,-16

03/01/2011, AAA, -4

05/01/2011, FFF,70

] ;

Data_Ordered:

NoConcatenate

LOAD

post

,item

,QTY

RESIDENT Data

ORDER BY item, post;

DROP TABLE Data;

Data_Final:

NoConcatenate

LOAD

post

,item

,QTY

,IF(item=Previous(item),NumSum(Peek('QTY_Accum'),QTY),QTY) as QTY_Accum

RESIDENT Data_Ordered ;

DROP TABLE Data_Ordered;

I've attached the file for you - hope it's what you're looking for.

Jason

Re: sum total in a field

Sat, 03 Sep 2011 13:43:49 GMT

Many thanks.

I will try this in real Application.

Bye