topic Creating cumulative percent variable in script in QlikView

Creating cumulative percent variable in script

Tue, 22 Sep 2015 01:52:28 GMT

Hi Guys,

I am new to Qlik, I have the following dataset:

Spend(containing 6 columns):

Managed Category	Material	MaterialCat	MatSpend	MCSpend	Perc
A	1A	A-1A	10000	22000	0.454545455
A	1D	A-1D	8000	22000	0.363636364
A	1B	A-1B	2000	22000	0.090909091
A	1C	A-1C	2000	22000	0.090909091
B	1E	B-1E	2000	7000	0.285714286
B	1F	B-1F	2000	7000	0.285714286
B	1G	B-1G	2000	7000	0.285714286
B	1T	B-1T	1000	7000	0.142857143

I need to create another column in the script that is a cumulative sum of the Perc column and get the result set like this

Material	Managed Category	CumulativePerc	MatSpend
1A	A	0.454545455	10000
1D	A	0.818181818	8000
1B	A	0.909090909	2000
1C	A	1	2000
1E	B	0.285714286	2000
1F	B	0.571428571	2000
1G	B	0.857142857	2000
1T	B	1	1000

I have tried using the Peek and Previous functions in the various ways I found in this forum, but everything results in one or more rows getting duplicated. I would really appreciate any help I can get, I am not able to solve this!

PFA the raw data file and the QVW:

Re: Creating cumulative percent variable in script

tresB — Tue, 22 Sep 2015 05:07:50 GMT

Try like:

If(Previous([Managed Category])=[Managed Category], RangeSum(Peek('CumulativePerc'), Perc), Perc) as CumulativePerc

Re: Creating cumulative percent variable in script

sunny_talwar — Tue, 22 Sep 2015 05:14:15 GMT

This script:

Table:

LOAD Category,

[Managed Category]

FROM

TestBook1.xlsx

(ooxml, embedded labels, table is Sheet1);

Join(Table)

LOAD Category,

Material,

Spend

FROM

TestBook1.xlsx

(ooxml, embedded labels, table is Sheet2);

Join(Table)

LOAD [Managed Category],

Sum(Spend)as MCSpend

Resident Table

Group By [Managed Category];

NewTable:

LOAD [Managed Category],

Material,

[Managed Category] & '-' & Material as MaterialCat,

Sum(Spend) as MatSpend,

MCSpend,

Sum(Spend)/MCSpend as Perc

Resident Table

Group By [Managed Category], Material, MCSpend;

DROP Table Table;

FinalTable:

LOAD *,

If(Peek('Managed Category') = [Managed Category], Alt(Peek('CumulativePerc'), 0) + Perc, Perc) as CumulativePerc

Resident NewTable

Order By [Managed Category], MatSpend desc;

DROP Table NewTable;

Output in Table Box:

Re: Creating cumulative percent variable in script

Thu, 24 Sep 2015 19:31:13 GMT

Thanks Sunny! It worked! Any idea why it wouldn't work without the Alt() ? Again, thanks a lot!

Re: Creating cumulative percent variable in script

ksharpes — Wed, 08 May 2019 10:12:13 GMT

Hello,

I am trying to do something similar to this, with a little bit added on at the end. Here's what i have so far

Classcreate1:
NoConcatenate Load
Item_ID,
If([LTM Total Value € (All)]>0,[LTM Total Value € (All)],0) as [Value]

resident Data_Values_New
where [Reporting Date]='31/03/2019';
Left Join (Classcreate1)
Load
Item_ID,
[Country]
resident Data_Item_New_3;

Classcreate2:
NoConcatenate Load*,
Recno() as [Order#]
Resident Classcreate1
Order By [Country]desc,[Value]desc,;
Drop table Classcreate1;

I am now trying to add in the percentage value, then cumulative percentage (based on country) and then eventually a categorization based on the cumulative percentage (something like the below)

If([**bleep**%]<.8,'Class A',if([**bleep**%]<.95,'Class B','Class C')) as Class

I tried replicating what you had posted but I cant get it to work, are you able to offer any assistance?

Re: Creating cumulative percent variable in script

ksharpes — Wed, 08 May 2019 12:12:04 GMT

never mind i did it!

Although now for some reason it failed on creating a search index, I have turned it off for now.

Classcreate1:
NoConcatenate Load
Item_ID,
If([LTM Total Value € (All)]>0,[LTM Total Value € (All)],0) as [Value],
[Stock Status2]
resident Data_Values_New
where [Reporting Date]='31/03/2019';

Left Join (Classcreate1)
Load
Item_ID,
[Country]

resident Data_Item_New_3;

Classcreate2:
NoConcatenate Load*,
Recno() as [Order#]
Resident Classcreate1
Order By [Country]desc,[Value]desc,;
Drop table Classcreate1;

Left Join (Classcreate2)
Load
[Country],
sum(Value) as [CountryValue]
Resident Classcreate2
Group by [Country];

Classcreate3:
NoConcatenate Load
[Item_ID],
[Country],
sum(Value/[CountryValue]) As Perc
Resident Classcreate2
Group by [Item_ID], [Country];
Left Join (Classcreate3)
Load
[Order#],
Item_ID,
[Value],
[CountryValue],
[Stock Status2]
Resident Classcreate2;

Drop table Classcreate2;

Classcreate4:
Load*,

IF([Stock Status2]='Available',If([CumulativePerc]<.8,'Class A',if([CumulativePerc]<.95,'Class B','Class C')),[Stock Status2]) as Class;

Load*,
If(Peek('Country') = [Country], Alt(Peek('CumulativePerc'), 0) + Perc, Perc) as CumulativePerc
Resident Classcreate3
Order by [Order#];
Drop table Classcreate3;

Data_Values_New2:
NoConcatenate Load*
Resident Data_Values_New;

Left join (Data_Values_New2)

Load [Item_ID],
[Class]
Resident Classcreate4;
Drop table Classcreate4;
Drop table Data_Values_New;