topic Re: Running Totals in QlikView

Running Totals

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

I have the data attaché herewith. I am using Qlikview 11.

I would want to create a table with running totals on the sales and the Projected sales to Look Like the table below. I would like the running total to be by each miner and to only accumulate within each financial Year

FY	Movement Date	Miner Details	Miner name	Opening balanve As At Opening FY	Quanty Sold	Quanty Projected	Running Total Quantity Sold	Running Total Quantity Projected	Stock On Hand
2016	18/04/2016	AZ111111	ax	500	100	120	100	120	400
2016	19/04/2016	AZ111112	ava	400	200	150	200	150	200
2016	20/04/2016	AZ111113	affa	200	150	160	150	160	50
2016	21/04/2016	AZ111114	axsd	300	20	20	20	20	280
2016	22/04/2016	AZ111115	asdsd	400	20	15	20	15	380
2016	18/04/2016	AZ111111	ax	500	50	40	150	160	350
2016	19/04/2016	AZ111112	ava	400	120	70	320	220	80
2016	20/04/2016	AZ111113	affa	200	120	90	270	250	-70
2016	21/04/2016	AZ111114	axsd	300	140	100	160	120	140
2016	22/04/2016	AZ111115	asdsd	400	70	50	90	65	310
2017	19/04/2017	AZ111112	ava	700	30	25	30	25	670
2017	20/04/2017	AZ111113	affa	500	20	20	20	20	480
2017	21/04/2017	AZ111114	axsd	300	25	20	25	20	275
2017	22/04/2017	AZ111115	asdsd	150	50	70	50	70	100
2017	26/04/2017	BZ000052	asdsd	170	30	15	30	15	140
2017	27/04/2017	BZ000053	scs	200	17	50	17	50	183
2017	28/04/2017	BZ000054	cxdf	300	21	30	21	30	279
2017	19/04/2017	AZ111112	ava	700	60	30	85	50	615
2017	20/04/2017	AZ111113	affa	500	15	20	65	90	435
2017	21/04/2017	AZ111114	axsd	300	48	75	78	90	222
2017	22/04/2017	AZ111115	asdsd	150	56	80	73	130	77
2017	26/04/2017	BZ000052	asdsd	170	78	100	99	130	71
2017	27/04/2017	BZ000053	scs	200	120	150	205	200	-5
2017	28/04/2017	BZ000054	cxdf	300	130	140	195	230	105

Re: Running Totals

sunny_talwar — Mon, 27 Mar 2017 19:29:35 GMT

Are you looking to do this in the script or front end of the application?

Re: Running Totals

devarasu07 — Mon, 27 Mar 2017 19:59:38 GMT

Hi,

try like this,

ABC Analysis in Qlikview

Re: Running Totals

Anonymous — Mon, 27 Mar 2017 20:00:55 GMT

you could use recursive rangesum():

if(FY=Above(total FY),
rangesum(above(total expression),sum(Quanty)),
sum(Quanty)
)

Re: Running Totals

ngrunoz — Tue, 28 Mar 2017 06:24:33 GMT

I would Prefer to do it in the script. but I will be glad to know how to do it in the front end too.

Re: Running Totals

ngrunoz — Thu, 30 Mar 2017 18:04:24 GMT

I have failed to Copy. But what I am trying to achieve is have the running total based on 2 dimension First being the (MovementDate and FY) and the second being the Miner details.

That is to say the Quantity Sold will only have to accumulate in the same FY and for the same Miner and not add for different miners in the same year.

Re: Running Totals

Anonymous — Thu, 30 Mar 2017 18:25:29 GMT

front end solution:

if(FY=Above(total FY) and [Miner name]=Above(TOTAL [Miner name]),
rangesum(above(total expression),sum(Quanty)),
sum(Quanty)
)

Re: Running Totals

Anonymous — Thu, 30 Mar 2017 18:47:16 GMT

script solution:

TEMP2: LOAD [Miner Details], [Miner name], FY, [Movement Date], Branch, Product, Quanty, Price, Name
FROM [String 2.xlsx] (ooxml, embedded labels, table is [Buying Sheet]);

NoConcatenate

TEMP: LOAD RowNo() as #,* Resident TEMP2 Order by FY,[Miner name];

DROP Table TEMP2;

NoConcatenate

FINAL:
LOAD
*,
if(Previous(([Miner name]))<>[Miner name] or (Previous([FY])<>[FY]),Quanty,rangesum(Peek('Quantity_ac'),Quanty)) as Quantity_ac
Resident TEMP;

DROP Table TEMP;

Re: Running Totals

ngrunoz — Thu, 04 May 2017 05:46:37 GMT

Hi Robin

I have encountered another challenge on the script version. when they is a record in between with a zero the script seems to be subtracting the previous value. how can I over come this?

Thanks

Re: Running Totals

ngrunoz — Thu, 04 May 2017 08:54:23 GMT

Sorry my bad. I have picked my error.