topic Re: Accumulate in dimensions in QlikView

Accumulate in dimensions

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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!!

Re: Accumulate in dimensions

antoniotiman — Mon, 03 Jul 2017 08:21:06 GMT

Hi,

maybe like this :

Re: Accumulate in dimensions

Anonymous — Mon, 03 Jul 2017 08:30:14 GMT

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.

Re: Accumulate in dimensions

jmvilaplanap — Mon, 03 Jul 2017 08:51:59 GMT

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

Re: Accumulate in dimensions

Anonymous — Mon, 03 Jul 2017 09:02:39 GMT

do you need everything calculated daywise in frontend?

Re: Accumulate in dimensions

Anonymous — Mon, 03 Jul 2017 09:13:13 GMT

can you please attach an example?

Re: Accumulate in dimensions

Anonymous — Mon, 03 Jul 2017 09:13:57 GMT

yes

Re: Accumulate in dimensions

Anonymous — Mon, 03 Jul 2017 09:16:07 GMT

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;

Re: Accumulate in dimensions

antoniotiman — Mon, 03 Jul 2017 09:28:24 GMT

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;

Re: Accumulate in dimensions

Anonymous — Mon, 03 Jul 2017 10:05:22 GMT

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

Re: Accumulate in dimensions

antoniotiman — Mon, 03 Jul 2017 10:09:11 GMT

Remove Month,

Re: Accumulate in dimensions

jmvilaplanap — Mon, 03 Jul 2017 10:12:27 GMT

Just one idea, replace the last two lines (if (year='2017' .... for the following

if (Year = year(today()), 1, 0) AS CY,

if (Year = year(today()) - 1, 1, 0) AS PY

Then will be useful forever,

regards

Re: Accumulate in dimensions

Anonymous — Mon, 03 Jul 2017 10:25:07 GMT

I removed month, which dimension should i pick instead?

because i need the month in my straight table

Re: Accumulate in dimensions

antoniotiman — Mon, 03 Jul 2017 10:30:58 GMT

In script,if Your Month Field is DateField

Then -> Month,

If Not then -> Month(Date#(Month,'MMM') ) as Month, or Month(Date) as Month

You need Month Field as Dual Field (Text,Num)

Re: Accumulate in dimensions

Anonymous — Mon, 03 Jul 2017 12:36:30 GMT

i am attaching the qvw and the excel file

Can you please help me with the script because i cant make it work.

Thank you

Re: Accumulate in dimensions

Anonymous — Mon, 03 Jul 2017 12:36:46 GMT

i am attaching the qvw and the excel file

Can you please help me with the script because i cant make it work.

Thank you

Re: Accumulate in dimensions

Anonymous — Mon, 03 Jul 2017 12:39:04 GMT

you mean my idea?

btw do you really need month as dimension or wouldn't year-month be better?!?

Re: Accumulate in dimensions

Anonymous — Mon, 03 Jul 2017 12:44:47 GMT

i tried your idea but it didn't work.

I attached the qvw can you apply it in my actual data???

Re: Accumulate in dimensions

Anonymous — Mon, 03 Jul 2017 12:51:06 GMT

I need only the month

Re: Accumulate in dimensions

Anonymous — Mon, 03 Jul 2017 13:25:23 GMT

have a look at the attachment: