17 Replies Latest reply: Dec 15, 2014 1:44 AM by Nayan Lalla

# 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  CODE Branch Name Sales Total Sales per Item ABC Bloemfontein 283 2,145 ABC Botswana 31 2,145 ABC Cape Town 268 2,145 ABC Durban 155 2,145 ABC East London 81 2,145 ABC Johannesburg 228 2,145 ABC Namibia 320 2,145 ABC Nelspruit 400 2,145 ABC Polokwane 80 2,145 ABC Port Elizabeth 189 2,145 ABC Swaziland 0 2,145 ABC Upington 110 2,145 ABC Total 2,145 2,145 DEF Bloemfontein 97 1,529 DEF Botswana 3 1,529 DEF Cape Town 138 1,529 DEF Durban 158 1,529 DEF East London 79 1,529 DEF Johannesburg 614 1,529 DEF Namibia 109 1,529 DEF Nelspruit 77 1,529 DEF Polokwane 15 1,529 DEF Port Elizabeth 85 1,529 DEF Swaziland 152 1,529 DEF Upington 2 1,529 DEF Total 1,529 1,529 3,674 1,529
• ###### Re: Creating a Total Column

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

• ###### 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

• ###### Re: Creating a Total Column

Check the attached app

Dimension: ITEM CODE,Branch Name

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

• ###### 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

• ###### Re: Creating a Total Column

Can you post sample app

• ###### 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

• ###### Re: Creating a Total Column

Hi Anbu

Attached herewith is the Sample Qlikview Model and an excel spreadhseet on which the Qlikview model is based on.

The Excel spreadheet has a sheet which contains sales per item per month per branch.

I have added a new field called "MonthIndex" .  This is use so that it will enable me to calculate sales for the last few months.

The chart table I created shows sales for the last 6 months and shows the "Average Last 6 Months".

In this sample model, i want the TOTAL for the "Average Last 6 Months".

So, Total for ABC = 11,025 ,  DEF =686 and GHI = 8429

kind regards

Nayan

• ###### Re: Creating a Total Column

How did you get Total for ABC = 11,025 ,  DEF =686 and GHI = 8429?

Can you post qvw which you tried to get avg of last 6 months?

• ###### Re: Creating a Total Column

Hi Anbu

When i looked at my post, i see the QV Model did not attached.  Please find attached QV Model.

kind regards

Nayan

• ###### Re: Creating a Total Column

(SUM(TOTAL<[Item Code]> {<[MonthIndex]={'\$(vMaxMIMinus6)'}>}[Sales Quantity])

+

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

+

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

+

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

+

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

+

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

Or

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

• ###### Re: Creating a Total Column

Hi Anbu

Thank you .  Much appreciated.  The first formula worked perfectly.

The second formula gave a total but for only 1 line.  Do you know why it does.

See attached.

Again, thank you for all your help.

kind regards

Nayan

• ###### 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

)

• ###### Re: Creating a Total Column

Hi Anbu

Thank you for all your help.  This is very useful for me.  Sorry for the delayed reply as it was the weekend and got to work this morning.

kind regards

Nayan

• ###### 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];
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

[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

• ###### 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

• ###### Re: Creating a Total Column

Sure check and let me know.

Regards

Anand