topic Re: Insert Sub Totals in QlikView

Insert Sub Totals

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I need some sub totals to straight table chart. I need some totals split by CSR and Customer Code.

I have highlighted the results I need in yellow and would like to know how I "break" the report up. It is pretty easy to do in crystal as you group the csr and then add another group for the customer and then add the totals in the group footer. Assume this can be done in Qlikview. Any help would be gratefully received

CSR	Sales Order Num	Sales Required Date	Sales Order Date	Item Code	Sales Ordered Quantity	Sales Outstanding Quantity	Sales Order Status	Customer Code
AE	137809	01/03/2016	24/02/2016	ITEM2	1500	1500	Authorised	B2029
AE	137809	01/03/2016	24/02/2016	ITEM3	1500	1500	Authorised	B2029
AE	139409	19/08/2016	29/07/2016	ITEM4	100000	0	Authorised	B2029
AE	139409	19/08/2016	29/07/2016	ITEM5	100000	0	Authorised	B2029
					203000	3000
AE	139198	22/07/2016	06/07/2016	ITEM6	1000	0	Authorised	B2030
AE	139198	22/07/2016	06/07/2016	ITEM7	1000	0	Authorised	B2030
AE	139198	22/07/2016	06/07/2016	ITEM8	1000	0	Authorised	B2030
AE	139476	16/09/2016	05/08/2016	ITEM9	50000	50000	Authorised	B2030
AE	139476	16/09/2016	05/08/2016	ITEM10	50000	50000	Authorised	B2030
AE	139477	16/09/2016	05/08/2016	ITEM11	50000	50000	Authorised	B2030
AE	139477	16/09/2016	05/08/2016	ITEM12	50000	50000	Authorised	B2030
AE	139962	24/10/2016	26/09/2016	ITEM13	6000	6000	Authorised	B2030
					209000	206000
CamillaHemmingsen	139107	15/08/2016	22/06/2016	F039384	300000	91456	Authorised	G7009
CamillaHemmingsen	139845	10/10/2016	14/09/2016	F041154	100000	26452	Authorised	G7009
CamillaHemmingsen	139845	10/10/2016	14/09/2016	S428-65	100000	26452	Authorised	G7009
					500000	144360
CamillaHemmingsen	137199	27/01/2016	17/12/2015	F039529	400	0	Authorised	OD001
					400	0
Carole George	137473	01/02/2016	20/01/2016	F014073	2000	600	Authorised	R18010
Carole George	137473	01/02/2016	20/01/2016	S679J-60	2000	600	Authorised	R18010
					4000	1200
Carole George	134606	05/05/2015	14/04/2015	F039463	100000	100000	Authorised	W23023
Carole George	134800	02/06/2015	28/04/2015	F012916/1	300000	58700	Authorised	W23023
Carole George	136186	06/10/2015	17/09/2015	F039657	100000	8800	Authorised	W23023
Carole George	136188	06/10/2015	17/09/2015	F039656	100000	8800	Authorised	W23023
					600000	176300

Re: Insert Sub Totals

Anil_Babu_Samineni — Wed, 12 Oct 2016 13:29:18 GMT

Try from Presentation Tab for the Field of Customer Code. Put Partial Sums for that

Re: Insert Sub Totals

Wed, 12 Oct 2016 13:41:00 GMT

sorry but I don't seem to have a sum option in the presentation tab

Re: Insert Sub Totals

krishnacbe — Wed, 12 Oct 2016 13:45:54 GMT

Hi,

Hope you are using Straight table, in straight table you can have sub total. Convert the chart to Pivot and use the Partial sum to get the Sub Total.

Re: Insert Sub Totals

beck_bakytbek — Wed, 12 Oct 2016 13:45:55 GMT

Hi Dave,

check this Topic: SUB Totals

i hope that helps

beck

Re: Insert Sub Totals

jonathandienst — Wed, 12 Oct 2016 13:47:56 GMT

While it may be possible to somehow get subtotals into a straight table, it will be more complicated and probably perform poorly than simply using a pivot table.

Re: Insert Sub Totals

Wed, 12 Oct 2016 13:52:34 GMT

don't have the option to load data in only have the front end

Re: Insert Sub Totals

Anil_Babu_Samineni — Wed, 12 Oct 2016 13:53:03 GMT

Like This

Re: Insert Sub Totals

Wed, 12 Oct 2016 13:55:16 GMT

that would be perfect I think

Re: Insert Sub Totals

jonathandienst — Wed, 12 Oct 2016 13:55:52 GMT

Like I said, use a pivot table...

Re: Insert Sub Totals

Anil_Babu_Samineni — Wed, 12 Oct 2016 14:28:13 GMT

Nice to hear,

I would also request you to consider marking the helpful response which you think might be helpful for others in the future. You don't have to overdo this but you can mark as many helpful responses as you would like

Qlik Community Tip: Marking Replies as Correct or Helpful

Re: Insert Sub Totals

Thu, 13 Oct 2016 11:03:03 GMT

I need to supress a couple of columns. the seconds that has authorised and the last one that has -1. If remove them I loose my data settings and it includes records I don't want. Any help would be gratefully appreciatedI

Re: Insert Sub Totals

Anil_Babu_Samineni — Thu, 13 Oct 2016 11:12:59 GMT

Dave Adlard wrote:

I need to supress a couple of columns.

Here, Columns OR Values

Re: Insert Sub Totals

Anil_Babu_Samineni — Thu, 13 Oct 2016 11:16:38 GMT

If YES, You might use Calculated Dimension that is

If( not wildmatch([Sales Order Status],'*Authorised*','-1'),[Sales Order Status])

If([Sales Order Status]<>'Authorised' and Dimension<>'-1',[Sales Order Status])

Re: Insert Sub Totals

Thu, 13 Oct 2016 11:20:08 GMT

the whole column

Re: Insert Sub Totals

Anil_Babu_Samineni — Thu, 13 Oct 2016 11:23:08 GMT

Dave, What you want to do overthere. I am not getting your point

HAve you tried my expression Above

Re: Insert Sub Totals

Thu, 13 Oct 2016 11:34:17 GMT

Sorry maybe I didn't make myself clear

I will try to explain again - I need to supress 2 columns from the report

I have managed to suppress the column that contains authorised.

The last column is an expression that only picks authorised records the code is simply

=[Sales Order Status] ='Authorised'

I've looked at your code and excuse my ignorance but what is the "Dimension". Is there not a tickbox like there is in a straight table to suppress a field