Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

TOTAL Column

Hi

Attached herewith is my sample QV Buying Model. I have attached my data source which is in excel.

In my chart table, i have a stock required column. I want to create a TOTAL STOCK REQUIRED column per item ( ie i want to create the column highlighted in yellow).

How do I do this?

kind regards

Nayan

Please note in the table below, I have hidden other columns , so that the table looks legible.

Item Code

Branch

Stock Required

TOTAL Stock Required

ABC

Bloemfontein

2 330

16 848

ABC

Botswana

403

16 848

ABC

Cape Town

2 821

16 848

ABC

Durban

0

16 848

ABC

East London

101

16 848

ABC

Johannesburg

9 440

16 848

ABC

Namibia

185

16 848

ABC

Nelspruit

21

16 848

ABC

Polokwane

1 536

16 848

ABC

Port Elizabeth

0

16 848

ABC

Swaziland

13

16 848

ABC

Upington

0

16 848

ABC

Total

16 848

16 848

DEF

Bloemfontein

0

913

DEF

Cape Town

210

913

DEF

Durban

0

913

DEF

East London

0

913

DEF

Johannesburg

422

913

DEF

Namibia

107

913

DEF

Nelspruit

0

913

DEF

Polokwane

0

913

DEF

Port Elizabeth

175

913

DEF

Swaziland

0

913

DEF

Upington

0

913

DEF

Total

913

913

GHI

Bloemfontein

0

8 315

GHI

Botswana

1 612

8 315

GHI

Cape Town

1 564

8 315

GHI

Durban

3 447

8 315

GHI

East London

0

8 315

GHI

Johannesburg

0

8 315

GHI

Namibia

315

8 315

GHI

Nelspruit

0

8 315

GHI

Polokwane

0

8 315

GHI

Port Elizabeth

1 106

8 315

GHI

Swaziland

0

8 315

GHI

Upington

272

8 315

GHI

Total

8 315

8 315

Total

26 075

26 075

1 Solution

Accepted Solutions

Re: TOTAL Column

Hi,

Use below expression to get Total Stock Required

sum(TOTAL<[Item Code]> aggr(rangemax(0,$(vAvgLast6Months)*$(No of Months)-sum([Quantity on Hand])),Branch,[Item Code]))

Regards

Regards,
Prashant Sangle
3 Replies

Re: TOTAL Column

Hi,

Use below expression to get Total Stock Required

sum(TOTAL<[Item Code]> aggr(rangemax(0,$(vAvgLast6Months)*$(No of Months)-sum([Quantity on Hand])),Branch,[Item Code]))

Regards

Regards,
Prashant Sangle

Re: TOTAL Column

You need to use Total Function with grouping of Item Code some thing like below

NUM(sum(TOTAL <[Item Code]> aggr(rangemax(0,$(vAvgLast6Months)*$(No of Months)-sum([Quantity on Hand])),Branch,[Item Code])),'##0')

Not applicable

Re: TOTAL Column

Than you Max and Manish.  Both your formulae worked.

kind regards

Nayan

Community Browser