Accumulate in dimensions

[Anonymous]

Hello experts!

I need you valuable help.

I have the above pivot in qlikview

Month	Sales CY	Sales PY	m2	Visitors
	13.774.659,17	30.855.173,02	8.236,22	677.175
Jan	2.695.856,62	2.401.932,99	8.401,45	127.612
feb	1.740.020,23	2.012.334,00	8.354,19	90.147
Mar	1.762.776,73	1.636.124,54	8.231,69	92.068
Apr	2.890.977,00	3.245.520,45	8.021,08	137.497
May	2.532.552,55	2.456.297,85	8.091,10	122.356
Jun	2.152.476,04	2.359.258,67	8.351,44	107.495
Jul	0,00	2.569.794,96	-	0
Aug	0,00	1.798.450,75	-	0
Sep	0,00	2.118.124,61	-	0
Oct	0,00	2.918.765,67	-	0
Nov	0,00	3.463.481,42	-	0
Dec	0,00	3.875.087,11	-	0

I want to add rows in dimensions field,

I have  to show the data in a table as cumulative sum of Month

I want the data to be displayed like this:

Month	Sales CY	Sales PY	m2	Visitors
	13.774.659,17	30.855.173,02	8.236,22	677.175
Jan	2.695.856,62	2.401.932,99	8.401,45	127.612
feb	1.740.020,23	2.012.334,00	8.354,19	90.147
2mos	4.435.876,85
Mar	1.762.776,73	1.636.124,54	8.231,69	92.068
3mos	6.198.653,58
Apr	2.890.977,00	3.245.520,45	8.021,08	137.497
4mos	9.089.630,58
May	2.532.552,55	2.456.297,85	8.091,10	122.356
5mos	11.622.183,13
Jun	2.152.476,04	2.359.258,67	8.351,44	107.495
6mos	13.774.659,17
Jul	0,00	2.569.794,96	-	0
7mos	13.774.659,17
Aug	0,00	1.798.450,75	-	0
8mos	13.774.659,17
Sep	0,00	2.118.124,61	-	0
9mos	13.774.659,17
Oct	0,00	2.918.765,67	-	0
10mos	13.774.659,17
Nov	0,00	3.463.481,42	-	0
11mos	13.774.659,17
Dec	0,00	3.875.087,11	-	0
12mos	13.774.659,17

I really appreciate any help you can provide!!

[antoniotiman]

Hi,

maybe like this :

[Anonymous]

Thanks for answering but as you can see above i have posted the final layout.

It is important for me to be exported like this, so i don't have to spend time working in excel sheet.

[jmvilaplanap]

You can use the previous function to create a formula with the previous row that you are using at the moment

For example:

• SalesCY + previous(AccSalesCY)  AS AccSalesCY

In the Load Statement

[Anonymous]

do you need everything calculated daywise in frontend?

[Anonymous]

can you please attach an example?

[Anonymous]

yes

[Anonymous]

here's a sample how it could basically work ...

LOAD Month(Datefield) as Month,Value;
LOAD * INLINE [
Datefield, Value
01.01.2017, 1
01.02.2017, 2
01.03.2017, 3       
01.04.2017, 4
01.05.2017, 5
01.06.2017, 6           
01.07.2017, 7
01.08.2017, 8
01.09.2017, 9       
01.10.2017, 10
01.11.2017, 11
01.12.2017, 12         
];

ISLAND:
load ' ' as DUMMY AutoGenerate 1;
load '  ' as DUMMY AutoGenerate 1;

[antoniotiman]

Table:
LOAD RowNo()*10 as Rowno,Month(Date#(Month,'MMM') ) as Month,
[Sales CY],
[Sales PY],
m2,
Visitors,
RangeSum([Sales CY],Peek(Mos)) as Mos
FROM
"https://community.qlik.com/message/1299178"
(html, codepage is 1252, embedded labels, table is @1)
Where Len(Trim(Month)) > 0;
Concatenate LOAD
Num(Month)*10+1 as Rowno,Num(Month)&'Mos' as Month,Mos as [Sales CY]
Resident Table where Month >= 2;

[Anonymous]

Thank you very much,

Below you can see the actual data i have in my script.

LOAD Building,

     Date,

     Year,

     Month,

     Day,

     Sales,

     Receipts,

     m2,

     Visitors,

     if(Year = '2017', 1, 0) as CY,

     if(Year = '2016', 1, 0) as PY

FROM

(qvd);

can you plz transform the above table you send me in this data,

I tried to do it by myself but it it ended up in script error:

Field names must be unique within table

Table:

LOAD RowNo()*10 as Rowno,Month(Date#(Month,'MMM') ) as Month,

Building,

     Date,

     Year,

     Month,

     Day,

     Sales,

     Receipts,

     m2,

     Visitors,

     if(Year = '2017', 1, 0) as CY,

     if(Year = '2016', 1, 0) as PY,

RangeSum(Sales,Peek(Mos)) as Mos

FROM

(qvd)

Where Len(Trim(Month)) > 0

and this dialog box:

Table not found error

Table 'Table' not found

Concatenate LOAD

Num(Month)*10+1 as Rowno,Num(Month)&'Mos' as Month,Mos as Sales

Resident Table where Month >= 2