topic Re: accumulate in straight table in QlikView

accumulate in straight table

syukyo_zhu — Wed, 28 Jan 2015 14:04:07 GMT

i have a table A in which i have my sales et sales of product A per date.

date	sales	product A
01/01/2015	100	50
02/01/2015	101	51
03/01/2015	102	52
04/01/2015	103	53
05/01/2015	104	54
06/01/2015	105	55
07/01/2015	106	56
08/01/2015	107	57
09/01/2015	108	58
10/01/2015	109	59

I want to calculate the rate cumulate product/sale, so i use "full accumulation" in my expression, it's ok for columns sales et product, but false for column rate cumulate

date	sales	product A	rate cumulate
01/01/2015	100	50	0,5
02/01/2015	201	101	1,002487562
03/01/2015	303	153	1,507438057
04/01/2015	406	206	2,01482722
05/01/2015	510	260	2,524631141
06/01/2015	615	315	3,036826263
07/01/2015	721	371	3,55138937
08/01/2015	828	428	4,068297583
09/01/2015	936	486	4,587528352
10/01/2015	1045	545	5,109059452

. i want to get a result like this one.

date	sales	product A	rate cumulate
01/01/2015	100	50	0,5
02/01/2015	201	101	0,502487562
03/01/2015	303	153	0,504950495
04/01/2015	406	206	0,507389163
05/01/2015	510	260	0,509803922
06/01/2015	615	315	0,512195122
07/01/2015	721	371	0,514563107
08/01/2015	828	428	0,516908213
09/01/2015	936	486	0,519230769
10/01/2015	1045	545	0,5215311

thanks

Re: accumulate in straight table

sunny_talwar — Wed, 28 Jan 2015 14:06:56 GMT

How is rate getting calculated here? Is it Product A/sales?

Best,

Re: accumulate in straight table

Anonymous — Wed, 28 Jan 2015 14:11:20 GMT

Did you try the following ?

[product A]/[Sales]

Re: accumulate in straight table

Anonymous — Wed, 28 Jan 2015 14:12:02 GMT

Here basically you are trying to use the chart labels and not Qlikview Data model fields

Re: accumulate in straight table

its_anandrjs — Wed, 28 Jan 2015 14:17:48 GMT

Hi,

In any straight chart try this way

Dim:- date

Expr1:- sum(sales) // With full accumulation select

Expr2:- sum([product A]) // With full accumulation select

Expr3:- Sum( [product A] ) / Sum( sales ) //No Accumulation

You expecting this output

Regards

Anand

Re: accumulate in straight table

sunny_talwar — Wed, 28 Jan 2015 14:22:48 GMT

Update: Expressions Used

1) RangeSum(Above(Sales, 0, 500))

2) RangeSum(Above([Product A], 0, 500))

3) RangeSum(Above([Product A], 0, 500))/RangeSum(Above(Sales, 0, 500))

All these are without accumulation.

Is this what you are looking for? I have used RangeSum(Above(...)) instead of accumulation.PFA

Best,

Re: accumulate in straight table

syukyo_zhu — Wed, 28 Jan 2015 14:24:10 GMT

yes

Re: accumulate in straight table

syukyo_zhu — Wed, 28 Jan 2015 14:25:19 GMT

it don't work

Re: accumulate in straight table

syukyo_zhu — Wed, 28 Jan 2015 14:25:48 GMT

yes but it don't work

Re: accumulate in straight table

Anonymous — Wed, 28 Jan 2015 14:27:03 GMT

Can you please share the application if it is ok?

Re: accumulate in straight table

Anonymous — Wed, 28 Jan 2015 14:29:58 GMT

I guess the labels are same as the data model fields in this case.....may be you can give a lable like 'Accumulated Sales' and Accumulated Product and then try to use the labels

Re: accumulate in straight table

syukyo_zhu — Wed, 28 Jan 2015 14:55:18 GMT

Or how can i calculate a number accumulate in my straight without use option "full accumulation"

Re: accumulate in straight table

sunny_talwar — Wed, 28 Jan 2015 14:56:45 GMT

Did you look at the sample application I posted? It shows you how you can calculate the cumulative numbers without using full accumulations.

Hope it helps.

Best,

Re: accumulate in straight table

sunny_talwar — Wed, 28 Jan 2015 14:58:18 GMT

Application attached

Expressions Used

1) RangeSum(Above(Sales, 0, 500))

2) RangeSum(Above([Product A], 0, 500))

3) RangeSum(Above([Product A], 0, 500))/RangeSum(Above(Sales, 0, 500))

All these are without accumulation.

Re: accumulate in straight table

syukyo_zhu — Wed, 28 Jan 2015 15:13:11 GMT

It works

thanks

Re: accumulate in straight table

sunny_talwar — Wed, 28 Jan 2015 15:14:45 GMT

Great, I am glad I was helpful.

Best,