topic Re: Problem with Sum in QlikView

Problem with Sum

Fri, 20 Nov 2015 07:57:41 GMT

I have a problem on my board the operation for sum not working:

Normally I leave QTY and I subtract every operation of movement of the result obtained previously.

Except here the problem is that it takes me all the time the value of the original stock for the subtraction instead of the result obtained after the movements.

The normal results are 507 - 102 = 405 not 429

If anyone have a solution

Re: Problem with Sum

krishna20 — Fri, 20 Nov 2015 09:43:47 GMT

Hi,

Can you please share sample app. It will helps to sort out the problem.

Re: Problem with Sum

PrashantSangle — Fri, 20 Nov 2015 09:47:33 GMT

Hi,

As Krishna suggest share your app or share your expression which are using.

Regards

Re: Problem with Sum

Fri, 20 Nov 2015 09:47:43 GMT

Hello ,

I have attached a few demo

thanks for the help

Re: Problem with Sum

PrashantSangle — Fri, 20 Nov 2015 10:19:45 GMT

As per your expression logic data is correct your

Qte_Stock_Inventory is 531 and

Qte_Mvt_Sum is -102

therefore final value is 531-102=429

Regards

Re: Problem with Sum

Fri, 20 Nov 2015 10:25:49 GMT

I want expression for calculate:

Qte_Stock_Inventory is 531

1st qte_Mvt is -24

531 - 24 : 507

2nd qte_Mvt is -102

507-102 : 405 not 429

etc....

Re: Problem with Sum

Kushal_Chawda — Fri, 20 Nov 2015 10:33:37 GMT

this is what you are looking for? see the last column

Re: Problem with Sum

rubenmarin — Fri, 20 Nov 2015 10:43:35 GMT

Hi, this expression for stock réel:

Sum(Qte_Stock_Inventaire)+ rangesum(above(Qte_Mvt_sum, 0, RowNo()))

returns this:

Re: Problem with Sum

Fri, 20 Nov 2015 12:32:07 GMT

Hi, the expression working but if i have many "Code_Article" the expression don't calculate for each "Code_Article" and i have this error. :

I think is missing a field in expression aggr but where i don't know.

Thanks to the help

Re: Problem with Sum

rubenmarin — Fri, 20 Nov 2015 13:05:04 GMT

My expression only works in a straight table, in a pivot table expanding one Code_Article will break the logic with above().

You can try if it works using 'TOTAL':

Sum(Qte_Stock_Inventaire)+ rangesum(above(TOTAL Qte_Mvt_sum, 0, RowNo()))

Remember to try expanding only one code_article, preferably in the middle (not the first or the last code_article)

Re: Problem with Sum

Fri, 20 Nov 2015 14:28:23 GMT

Not Working.

For explain i post an exemple;

i need to have this results from the expression :

for Code_Article D000140 normally the correct answer is 234 but i have -768

I think is the pivot table trouble my expression but i need the expression work up to the month

Thanks to the help

Re: Problem with Sum

p_verkooijen — Fri, 20 Nov 2015 14:39:48 GMT

In a pivot use Before instead of Above

Re: Problem with Sum

Mon, 23 Nov 2015 07:06:04 GMT

1/ If i use this expression :

Sum(Qte_Stock_Inventaire)+ rangesum(Above(TOTAL Qte_Mvt , 0, RowNo()))

I found a good results when i select one "Code_Article" but for all "Code_Article" the results not good

2/ If i use this expression :

Sum(Qte_Stock_Inventaire)+ rangesum (Before(TOTAL Qte_Mvt , 0, RowNo())) my results is wrong

exemple for the "Code_Article" D000140

Normally i found 234 in Stock_reel

But i found 306 with expression with BEfore.

Thanks to the help

Re: Problem with Sum

rubenmarin — Mon, 23 Nov 2015 08:23:41 GMT

The expression can be:

Top(Qte_Stock_Inventaire)+ rangesum(above(TOTAL Sum(Qte_Mvt_sum), 0, RowNo(TOTAL)))

But as said before, if you expand one article it will break the 'Above' logic, condider if you can make the chart using a straight table instead of pivot table or the option to precalculate the inventory by article and date in the script.

Re: Problem with Sum

Mon, 23 Nov 2015 09:28:16 GMT

Have you an exemple of script to precalculate the inventory by article and date?

thanks to the help

Re: Problem with Sum

rubenmarin — Tue, 24 Nov 2015 08:03:54 GMT

Not sure if having a precalculated stock can fit your requirements but you can precalculate with something like:

// Suppposing your fact table is called 'Facts' (in this table you have all the fields):

LOAD *,

If(RowNo()=0, Qte_Stock_Inventaire, 0) + RangeSum(Peek([stock reel acum]), Qte_Mvt_sum) as [stock reel acum]

Resident Facts order by Code_Article, Date_Mvt;

In the pivot table yo can try this expression:

FirstSortedValue([stock reel acum], -Date_Mvt)

Note that the values are precalculated so, no matter what selections you have, there will be a precalculated value for each article and date.

Re: Problem with Sum

Mark_Little — Tue, 24 Nov 2015 08:36:17 GMT

HI,

If this is on one table in the load script or can be put into one then you can think about doing these calculation on load.

Using Peek() or Previous().

Something like

IF(RowNO = 1, Stock_reel,

Previous(Stock_reel) - Qte_Mvt_sum) as RunningBalance

Mark