Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Creating cumulative percent variable in script

Hi Guys,

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

Spend(containing 6 columns):

      

Managed CategoryMaterialMaterialCatMatSpendMCSpendPerc
A1AA-1A10000220000.454545455
A1DA-1D8000220000.363636364
A1BA-1B2000220000.090909091
A1CA-1C2000220000.090909091
B1EB-1E200070000.285714286
B1FB-1F200070000.285714286
B1GB-1G200070000.285714286
B1TB-1T100070000.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 

   

MaterialManaged CategoryCumulativePercMatSpend
1AA0.45454545510000
1DA0.8181818188000
1BA0.9090909092000
1CA12000
1EB0.2857142862000
1FB0.5714285712000
1GB0.8571428572000
1TB11000

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:

1 Solution

Accepted Solutions
Highlighted

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:

Capture.PNG

View solution in original post

5 Replies
Highlighted
MVP
MVP

Try like:

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

Highlighted

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:

Capture.PNG

View solution in original post

Highlighted
Not applicable

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

Highlighted
Creator
Creator

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?

Highlighted
Creator
Creator

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;