topic Re: Accumulate calculation in QlikView

Accumulate calculation

geogou1973 — Wed, 27 Apr 2016 08:41:39 GMT

Hello.

I have a problem with the accumulate calculation.

I have the following expression that calculates the net sales by sales_date

Sum({1<SALES_YEAR={'$(=vYear)'},SALES_MONTH={'$(=vMonth)'},SALES_WEEK={'<$(maxweek)'},FORMAT={'HYPFL','SUPER','PROXI','CASHC','HYPMI'}-{'FRANC'}>}SALES_NET) and i give the label

Net Sales Month to Date

Th following expression calculates the margin by date

(Sum({1<SALES_YEAR={'$(=vYear)'},SALES_MONTH={'$(=vMonth)'},SALES_WEEK={'<$(maxweek)'},FORMAT={'HYPFL','SUPER','PROXI','CASHC','HYPMI'}-{'FRANC'}>}SALES_NET)-

Sum({1<SALES_YEAR={'$(=vYear)'},SALES_MONTH={'$(=vMonth)'},SALES_WEEK={'<$(maxweek)'},FORMAT={'HYPFL','SUPER','PROXI','CASHC','HYPMI'}-{'FRANC'}>}COGS)) and i give the label

Margin Month to Date

in these two expression i check the option Accumute with step back 31 and works perfect.

I want to divide the margin with the net sales in order to have the margin % with the folowing expression

[Margin Month to Date]/[Net Sales Month to Date] but the numbers are not correct. the numbers are the same like the Net Sales and Margin without Accumulation.

How can i have the correct calculation when i divide the Accumulative Margin with the Net Sales ?

Thank you in advance.

Re: Accumulate calculation

sunny_talwar — Wed, 27 Apr 2016 08:48:52 GMT

I think you will have to use RangeSum(Above()) function to calculate the margin % here

May be this:

RangeSum(Above(Sum({1<SALES_YEAR={'$(=vYear)'},SALES_MONTH={'$(=vMonth)'},SALES_WEEK={'<$(maxweek)'},FORMAT={'HYPFL','SUPER','PROXI','CASHC','HYPMI'}-{'FRANC'}>}SALES_NET)-

Sum({1<SALES_YEAR={'$(=vYear)'},SALES_MONTH={'$(=vMonth)'},SALES_WEEK={'<$(maxweek)'},FORMAT={'HYPFL','SUPER','PROXI','CASHC','HYPMI'}-{'FRANC'}>}COGS), 0, 31))/

RangeSum(Above(Sum({1<SALES_YEAR={'$(=vYear)'},SALES_MONTH={'$(=vMonth)'},SALES_WEEK={'<$(maxweek)'},FORMAT={'HYPFL','SUPER','PROXI','CASHC','HYPMI'}-{'FRANC'}>}SALES_NET), 0, 31))

Re: Accumulate calculation

geogou1973 — Wed, 27 Apr 2016 08:59:40 GMT

Hello Sunny

This formula is doing the same like the accumulation when you check the option in the expression tab.

I don't want to accumulate the division. i want to divide correctly the amounts from the columns that a have done the accumulation.

Thank you for your answer

Re: Accumulate calculation

sunny_talwar — Wed, 27 Apr 2016 09:05:57 GMT

Is there a possibility to share a sample with expected output?

Re: Accumulate calculation

geogou1973 — Wed, 27 Apr 2016 09:18:50 GMT

The last column is the correct division for the column [Month to Date System Margin]/[Month to Date Net Sales]

The column before is incorrect. How can i have the results like the last column ?

Date	Net Sales	System Margin	System Margin %	Month to Date Net Sales	Month to Date System Margin	Month to Date System Margin %
01/04/2016	641.800	12.532	1,95%	641.800	12.532	1,95%	1,95%
02/04/2016	861.775	20.627	2,39%	1.503.575	33.159	4,16%	2,21%
03/04/2016	7.337	440	6,00%	1.510.912	33.599	6,38%	2,22%
04/04/2016	557.753	18.371	3,29%	2.068.665	51.970	8,89%	2,51%
05/04/2016	483.118	16.703	3,46%	2.551.784	68.673	11,59%	2,69%
06/04/2016	460.408	14.325	3,11%	3.012.191	82.998	14,34%	2,76%
07/04/2016	467.947	16.315	3,49%	3.480.139	99.313	17,19%	2,85%
08/04/2016	533.182	16.090	3,02%	4.013.321	115.402	20,07%	2,88%
09/04/2016	749.153	28.509	3,81%	4.762.474	143.911	23,09%	3,02%
10/04/2016	7.639	536	7,02%	4.770.113	144.447	26,12%	3,03%
11/04/2016	516.642	19.239	3,72%	5.286.754	163.686	29,22%	3,10%
12/04/2016	435.317	17.403	4,00%	5.722.071	181.089	32,38%	3,16%
13/04/2016	436.645	13.781	3,16%	6.158.717	194.871	35,54%	3,16%
14/04/2016	481.082	4.175	0,87%	6.639.799	199.046	38,54%	3,00%
15/04/2016	532.450	15.314	2,88%	7.172.249	214.359	41,53%	2,99%
16/04/2016	754.253	18.167	2,41%	7.926.502	232.527	44,46%	2,93%
17/04/2016	8.476	353	4,16%	7.934.978	232.880	47,40%	2,93%
18/04/2016	548.223	11.720	2,14%	8.483.201	244.600	50,28%	2,88%
19/04/2016	499.745	6.112	1,22%	8.982.946	250.712	53,07%	2,79%
20/04/2016	511.940	1.217	0,24%	9.494.886	251.929	55,73%	2,65%
21/04/2016	661.970	-36.386	-5,50%	10.156.856	215.543	57,85%	2,12%
22/04/2016	759.508	-29.878	-3,93%	10.916.364	185.666	59,55%	1,70%
23/04/2016	1.118.403	-47.725	-4,27%	12.034.766	137.940	60,70%	1,15%
24/04/2016	474.060	-34.363	-7,25%	12.508.826	103.577	61,52%	0,83%
	12.508.826	103.577	0,83%	12.508.826	103.577	0,00%

Re: Accumulate calculation

sunny_talwar — Wed, 27 Apr 2016 09:26:39 GMT

Please find attached the two options available to you

Re: Accumulate calculation

geogou1973 — Wed, 27 Apr 2016 09:42:51 GMT

It is correct Sunny,

thank you very much for your help.

Re: Accumulate calculation

sunny_talwar — Wed, 27 Apr 2016 09:45:14 GMT

No Problem