topic Re: Creating a Total Column in QlikView

Creating a Total Column

Thu, 11 Dec 2014 16:21:09 GMT

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

anbu1984 — Thu, 11 Dec 2014 16:42:19 GMT

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

Re: Creating a Total Column

Thu, 11 Dec 2014 16:50:09 GMT

or you can use

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

Re: Creating a Total Column

its_anandrjs — Thu, 11 Dec 2014 16:56:21 GMT

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

Re: Creating a Total Column

Fri, 12 Dec 2014 07:14:01 GMT

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

Fri, 12 Dec 2014 08:17:15 GMT

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

anbu1984 — Fri, 12 Dec 2014 08:47:26 GMT

Check the attached app

Dimension: ITEM CODE,Branch Name

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

Re: Creating a Total Column

Fri, 12 Dec 2014 10:38:53 GMT

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

anbu1984 — Fri, 12 Dec 2014 10:42:17 GMT

Can you post sample app

Re: Creating a Total Column

Fri, 12 Dec 2014 10:48:00 GMT

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

Fri, 12 Dec 2014 12:54:21 GMT

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

Re: Creating a Total Column

anbu1984 — Fri, 12 Dec 2014 13:13:13 GMT

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

Fri, 12 Dec 2014 13:23:12 GMT

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

anbu1984 — Fri, 12 Dec 2014 14:03:35 GMT

(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

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

Fri, 12 Dec 2014 14:21:45 GMT

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

its_anandrjs — Fri, 12 Dec 2014 16:46:31 GMT

Sure check and let me know.

Regards

Anand

Re: Creating a Total Column

anbu1984 — Mon, 15 Dec 2014 05:19:55 GMT

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

Mon, 15 Dec 2014 06:44:29 GMT

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