Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
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
sunny_talwar

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
tresesco
MVP
MVP

Try like:

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

sunny_talwar

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

Not applicable
Author

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

ksharpes
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?

ksharpes
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;