Creating a Total Column

[#1]

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

[#2]

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

Thanking you in advance.

kind regards

Nayan

[anbu1984]

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?

[#4]

Hi Anbu

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

kind regards

Nayan

[anbu1984]

(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

[#6]

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

[its_anandrjs]

Sure check and let me know.

Regards

Anand

[anbu1984 · Accepted solution]

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

)

[#9]

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