topic [double-counting] Subtotal function in Excel not reflected in QV in QlikView

[double-counting] Subtotal function in Excel not reflected in QV

Fri, 20 Nov 2020 18:02:30 GMT

Hi everyone, need your help here...

I have this table in excel:

Account Code

Category/Item Description

Approved Budget

220700	Office Supplies, Stationery and Periodicals	800.00
220701	Office Supplies, Stationery and Periodicals	800.00
220800	Teaching Materials, Library & Consumable Supplies	52,828.00
220801	Teaching Expenses, Materials and Consumables	50,728.00
220803	Library Books and Supplies	-
220805	Subscription and Membership Fee	2,100.00

Because in Excel the Subtotal function only calculates other subtotal columns, the total here should be 83,628. But when I upload it to QV, the sum is 107,256 (it sums up all the rows).

I would like my QV to reflect the true total (subtotals added together) as per the excel sheet.

Is there anyway that I can get this resolved?

Thank you!

PS: I am really new to QV, appreciate your kind support

Re: [double-counting] Subtotal function in Excel not reflected in QV

Anonymous — Mon, 14 Jul 2014 08:51:15 GMT

Hi,

You can exclude them by using where clause. See below,

LOAD

[Account Code],

[Category/Item Description],

[Approved Budget]

From <Table>

Where Not Match([Account Code], 220700, 220800);

Re: [double-counting] Subtotal function in Excel not reflected in QV

Mon, 14 Jul 2014 09:32:43 GMT

Thanks!

What if this is just 2 of tens of accounts? Is there a way that I can edit the expressions section? Because the above is used to generate a chart, and I cannot reconcile the figures as the total is always huge compared to the actual total.

Thank you!