Pivot table expression changes when items expanded/contracted

[jjwild00]

I have a pivot table when the value of a simple expression changes based on whether other areas of the pivot table are expanded or contracted.

Here is what it looks like with all values expanded.  Note how 37 = 10 + 21 + 6.

Connect Status	Attempt Number	Count	Total Count
Connected	1	10	37
	2	21	42
	3	32	94
Aborted	1	21	37
	2	14	42
	3	54	94
Timed Out	1	6	37
	2	7	42
	3	8	94

Now if I contract Time Out, the value of Total Count changes.  Now it is 31 = 10 + 21.

Connect Status	Attempt Number	Count	Total Count
Connected	1	10	31
	2	21	35
	3	32	86
Aborted	1	21	31
	2	14	35
	3	54	86
Timed Out		21	173

Total Count is set to count(total <Attempt Number> AttemptID).

Its value should not change based on how other rows are expanced/contracted.

Any ideas?

[giakoum]

a sample app would help

[Oleg_Troyansky]

It looks like your expression depends on the fact that Attempt Number is the same. Once you collapse Time out, the Attempt Number becomes unavailable, and therefore the expression returns partial results. Try replacing the sum(total ...) with an Advanced Aggregation calculation:

sum(aggr(count(AttemptID), [Connect Status], [Attempt Number]))

I haven't tested it, but I think it should work.

cheers,

Oleg Troyansky

www.masterssummit.com - take your QlikView skills to the next level!

[jjwild00]

This calculates the correct number and it is not affected by the expand/contract status of the chart.

However, it only puts up the value in one row of the chart for each attempt number.  The other rows are 0.

In the example above, the correct Total Counts are attempt 1 = 37, attempt 2 = 42 and attempt 3 = 94.

It displays the Total Count of 37 for attempt 1 for the Connected status only.  The Total Count for attempt 1 for the Aborted status and the Timed Out status is 0.

So we are closer, but not yet there.

Any ideas?

Thanks,

John

[Oleg_Troyansky]

as Ioannis said above, a sample app would help

[jjwild00]

Here is a sample app.

You can see the count change when you expand and contract the sections.

Then the last column is an aggr function.  It shows the correct values, but only on one line for each AttemptCount.

Thanks,

John

[Oleg_Troyansky]

I don't see any attachments... tried it in two browsers.

[Oleg_Troyansky]

OK, got it. Here is the corrected formula:

sum( total <AttemptNumber> aggr(sum(Count), ConnectStatus, [AttemptNumber]))

What was missing in your version is listing ConnectStatus in the list of AGGR dimensions - his is what I call "unsafe AGGR", you should remember it from the Masters Summit in Chicago.

What was missing in my first guess is the  total <AttemptNumber>, since you wanted to summarize all the values for the same Attempt number...

I believe this one should work

cheers,

Oleg

[jjwild00]

This gives the correct values but is still affected when you expand/contract the ConnectStatuses.

I understand aggr as a way to do summations, etc, separate from the specific chart you are in.  Apparently, it is looking at the records in the chart you are in and those records lose any dimensions which are contracted.

[jjwild00 · Accepted solution]

I found a solution.

One of my users found this.  I guess I knew too much to try this solution.

To reiterate:

This expression only totals for AttemptNumbers which were displayed in the pivot table.  Expanding/contracting the pivot table changed the value:  sum(total <AttemptNumber> Count)

The following expression gave me the correct totals, but would only show for one line containing AttemptNumber: sum(aggr(sum(Count), [AttemptNumber])).  Other lines for that AttemptNumber would incorrectly show 0.

This expression showed the correct total on all lines that have AttemptNumber on it: max(total <AttemptNumber> aggr(sum(Count), [AttemptNumber])).

Download the sample app attached and expand/contract lines in the table to see the results.