topic Re: Subtotals in QlikView

Subtotals

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I was wondering if I could get some help with QlikView please.

I have a pivot table with numerous products and sales data. I would like to add a subtotal at the end of each product as well as a grand total.

Please see example below.

Current data

Product Name	Amount sold	Sales
Product 1	51	2000
Product 1	21	1000
Product 2	34	1500
Product 2	12	500
Product 2	10	450
Grand total	128	5450

Desired result

Product Name	Amount sold	Sales
Product 1	51	2000
Product 1	21	1000
Product 1 total	72	3000
Product 2	34	1500
Product 2	12	500
Product 2	10	450
Product Total	56	2450
Grand Total	90	5450

Many thanks,

Esther

Re: Subtotals

Anil_Babu_Samineni — Wed, 25 Oct 2017 03:21:22 GMT

May be try something like below?

Main:

LOAD *, RowNo() as ID Inline [

Product Name, Amount sold, Sales

Product 1, 51, 2000

Product 1, 21, 1000

Product 2, 34, 1500

Product 2, 12, 500

Product 2, 10, 450

];

Concatenate (Main)

LOAD RowNo() as ID,'Product 1 Total' as [Product Name], Sum([Amount sold]) as [Amount sold], Sum(Sales) as Sales Resident Main Where

[Product Name] = 'Product 1' Order By [Product Name];

Concatenate (Main)

LOAD RowNo() as ID,'Product 2 Total' as [Product Name], Sum([Amount sold]) as [Amount sold], Sum(Sales) as Sales Resident Main Where

[Product Name] = 'Product 2' Group By [Product Name] Order By [Product Name];

Re: Subtotals

devarasu07 — Wed, 25 Oct 2017 03:47:52 GMT

Hi,

What is use of this report format? by default if we do sum(Sales) of product then it will group by values so u can't get desired output. try to do qlikview by default partial sum option (best way)

if you wish to see without group by value then use that table object with back end script instead of stright/pivot table method.