Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Creating a Total Column


Hi

I have a sample of a straight table. (see below).  Sales per item per Branch.

I want to create a total column per item ( ie i want to create the column highlighted in yellow).

How do I do this.

kind regards

Nayan

ITEM  CODEBranch NameSalesTotal Sales per Item
ABCBloemfontein2832,145
ABCBotswana312,145
ABCCape Town2682,145
ABCDurban1552,145
ABCEast London812,145
ABCJohannesburg2282,145
ABCNamibia3202,145
ABCNelspruit4002,145
ABCPolokwane802,145
ABCPort Elizabeth1892,145
ABCSwaziland02,145
ABCUpington1102,145
ABCTotal2,1452,145
DEFBloemfontein971,529
DEFBotswana31,529
DEFCape Town1381,529
DEFDurban1581,529
DEFEast London791,529
DEFJohannesburg6141,529
DEFNamibia1091,529
DEFNelspruit771,529
DEFPolokwane151,529
DEFPort Elizabeth851,529
DEFSwaziland1521,529
DEFUpington21,529
DEFTotal1,5291,529
3,6741,529
1 Solution

Accepted Solutions
anbu1984
Honored Contributor III

Re: Creating a Total Column

If(IsNull(Aggr(SUM( {<[MonthIndex]={'$(vMaxMIMinus6)'}>}[Sales Quantity])

+

SUM( {<[MonthIndex]={'$(vMaxMIMinus5)'}>}[Sales Quantity])

+

SUM( {<[MonthIndex]={'$(vMaxMIMinus4)'}>}[Sales Quantity])

+

SUM( {<[MonthIndex]={'$(vMaxMIMinus3)'}>}[Sales Quantity])

+

SUM( {<[MonthIndex]={'$(vMaxMIMinus2)'}>}[Sales Quantity])

+

SUM( {<[MonthIndex]={'$(vMaxMIMinus1)'}>}[Sales Quantity]),[Item Code])/ 6

),Above(Column(9),1,1),Aggr(SUM( {<[MonthIndex]={'$(vMaxMIMinus6)'}>}[Sales Quantity])

+

SUM( {<[MonthIndex]={'$(vMaxMIMinus5)'}>}[Sales Quantity])

+

SUM( {<[MonthIndex]={'$(vMaxMIMinus4)'}>}[Sales Quantity])

+

SUM( {<[MonthIndex]={'$(vMaxMIMinus3)'}>}[Sales Quantity])

+

SUM( {<[MonthIndex]={'$(vMaxMIMinus2)'}>}[Sales Quantity])

+

SUM( {<[MonthIndex]={'$(vMaxMIMinus1)'}>}[Sales Quantity]),[Item Code])/ 6

)

17 Replies
anbu1984
Honored Contributor III

Re: Creating a Total Column

=Aggr(Sum(Sales),[ITEM CODE])

Not applicable

Re: Creating a Total Column

or you can use

=sum(TOTAL <[ITEM CODE]> Sales)

Re: Creating a Total Column

Another way if you add the Sum fields in the load script

Tab1:
LOAD    [ITEM CODE], [Branch Name], Sales, [Total Sales per Item];
LOAD * INLINE [
    ITEM CODE, Branch Name, Sales, Total Sales per Item
    ABC, Bloemfontein, 283, 2145
    ABC, Botswana, 31, 2145
    ABC, Cape Town, 268, 2145
    ABC, Durban, 155, 2145
    ABC, East London, 81, 2145
    ABC, Johannesburg, 228, 2145
    ABC, Namibia, 320, 2145
    ABC, Nelspruit, 400, 2145
    ABC, Polokwane, 80, 2145
    ABC, Port Elizabeth, 189, 2145
    ABC, Swaziland, 0, 2145
    ABC, Upington, 110, 2145
    DEF, Bloemfontein, 97, 1529
    DEF, Botswana, 3, 1529
    DEF, Cape Town, 138, 1529
    DEF, Durban,    158, 1529
    DEF, East London, 79, 1529
    DEF, Johannesburg, 614, 1529
    DEF, Namibia, 109, 1529
    DEF, Nelspruit, 77, 1529
    DEF, Polokwane, 15, 1529
    DEF, Port Elizabeth, 85, 1529
    DEF, Swaziland, 152, 1529
    DEF, Upington, 2, 1529
];

Left Join

LOAD
[ITEM CODE],
sum( Sales) as [Total Sum] //For Total Sum
Resident Tab1 Group By [ITEM CODE];

And then in chart

Dim1:-[ITEM CODE], [Branch Name]

Expre:- Sum(Sales)

Expre:- [Total Sum]

Or simple in the front end

Sum(Sales)

Sum(TOTAL <[ITEM CODE]> Sales)

Regards

Anand

Not applicable

Re: Creating a Total Column

Hi All

I send the post before I went home last night, hence the delayed reply.   Thank you for your responses.   Will try the scripting and let you know .

kind regards

Nayan

Not applicable

Re: Creating a Total Column

Hi Anbu

I've tried the formula , however it gives the grand total. ie 3674 for ABC & DEF.  I require for ABC the total to show 2145 and for DEF to show 1529.

kind regards

Nayan

anbu1984
Honored Contributor III

Re: Creating a Total Column

Check the attached app

Dimension: ITEM CODE,Branch Name

Expression: Aggr(Sum(Sales),[ITEM CODE])


145469.png

Not applicable

Re: Creating a Total Column

Hi Anbu

In my QV Model, my Sales is a calculated field. How do I apply your formula.  It works if it is a proper field from the loading script.  However it does not work as mines on the front end is a calculated field.

kind regards

Nayan


anbu1984
Honored Contributor III

Re: Creating a Total Column

Can you post sample app

Not applicable

Re: Creating a Total Column

Hi Anbu

Will compile a sample model for you. I'll create a mock database in excel and link the QV Model to it.

kind regards

Nayan


Community Browser