topic Re: Unexpected total results in QlikView

Unexpected total results

dukane24 — Thu, 05 Oct 2017 21:37:35 GMT

Hello,

I am having a frustrating experience with a pivot table, specifically the totals, and I was hoping someone could help me to get it to work as expected.

Basically I've got a pivot table set up that looks at work types for a number of people, totals their inventory, and determines how long the work will take them based on a time standard. So there are three expressions:

Inventory - The Sum of the work

Standard - Sum of Time taken to do the work divided by number of transactions (basically the average time taken to do the work)

Total time - Inventory times Standard

The resulting table looks like the one below. What's confusing me is the total column - the total reflects the sum of the inventory, and the standard total is close to an average, but the total time is in some cases nowhere need a total of the times across the various items types, in this case Type 1 and Type 2. In the actual app, I have the totals for the Inventory and Standard hidden, so the user will only see the total for the Total Time. What I'm looking for is for the total for the Total Time to be an actual total of the time for all of the item types.

I appreciate any help you can offer in advance.

Name		Type 1	Type 2	Total
Tom	Inventory	1820	129	1949
	Standard	2.67	1.71	2.16
	Total Time	4851.95	220.7	4217.16
Jane	Inventory	269	18	287
	Standard	1.79	1	1.14
	Total Time	481.92	17.99	327.38
Bob	Inventory	484	22	506
	Standard	3.18	1.22	2.35
	Total Time	1539.7	26.87	1191.55

Re: Unexpected total results

vishsaggi — Thu, 05 Oct 2017 22:03:40 GMT

can you share a sample to look into? What is your actual expression for Total time? May be try like

= Sum(Inventory) * Sum(Standard)

Re: Unexpected total results

marcus_sommer — Fri, 06 Oct 2017 12:04:19 GMT

You will need an aggr-function for it. Probably something like:

sum(aggr(sum([Total Time]), Name, Type))

- Marcus

Re: Unexpected total results

dukane24 — Wed, 11 Oct 2017 20:55:22 GMT

Thank you Marcus! This did just what I needed.

Re: Unexpected total results

dukane24 — Wed, 11 Oct 2017 20:59:40 GMT

Thank you for replying - this is very close to what I was using - it was closer to Sum(Inventory) * Avg(Process_time). At a granular level the results were as expected, but the total was more of the sum of the inventory times the average of the process times for the various work item types once that dimension was added. The users are expecting to see the sum of the granular (broken down by the dimension) Total Time.

Re: Unexpected total results

vishsaggi — Wed, 11 Oct 2017 21:37:54 GMT

Got it. Glad it worked for you.