Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability:
**TELL ME MORE!**

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Partial Sum in Pivot Table

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Not applicable

2010-05-27
11:11 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Partial Sum in Pivot Table

Hello everybody,

I have the following formula in a pivot table

=if((sum((if((not Auftragsart='RE') and

(not Auftragsart='ZRE') and

(not Bundle = 'K') and

(len([Absagegrund #]) < 2) and

(not Auftrag = '0100448925') and

(not Auftrag = '0100506496') and

(not Auftrag = '0100458473') and

([Jahr(%Datum)] > 2006),1,0) * Auftragsmenge)))=0,null(),

(sum(Auftragsmenge*[VK-Preis]) + sum(Auftragsmenge*Handling)))

the sum for each position is correct. But the total sum calculate all values, even the values that are eliminate with the If-clause.

I hope my explanation was not to abstract. [:)]

Tanja

1,399 Views

7 Replies

Not applicable

2010-05-27
12:03 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi Tanja

You got for sure a lengthy condition list.

I assume for the total calculation your Auftragsmenge is > 0 and so everything gets counted.

Can you add a flag 0/1 during the data load (or qvd creation) representing all your conditions.

You can then simply multiply Auftragsmenge with this flag to eliminate the unwanted values and this should then also work with your total.

You could of course also duplicate all conditions for the 2 Auftragsmenge values but that will make it even more cluttered.

Regards

Jürg

400 Views

johnw

Champion III

2010-05-28
08:55 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

A general solution to this kind of problem is this:

sum(aggr(your current expression, your chart dimensions))

Each individual row is already aggregated by your chart dimensions, so this has no effect at the row level. It only has an affect on partial sums, where it is explicitly applying your expression to every row instead of applying your expression on the total line.

400 Views

Not applicable

2010-05-29
05:06 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi

looking at your formula, would not also this give you the correct result?

=sum(if((not Auftragsart='RE') and

(not Auftragsart='ZRE') and

(not Bundle = 'K') and

(len([Absagegrund #]) < 2) and

(not Auftrag = '0100448925') and

(not Auftrag = '0100506496') and

(not Auftrag = '0100458473') and

([Jahr(%Datum)] > 2006), Auftragsmenge * ([VK-Preis]+Handling) ))

Juerg

400 Views

Anonymous

Not applicable

2010-12-09
10:12 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

400 Views

Not applicable

2010-12-09
10:59 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

ojo con los () Prueba

sum(aggr(your current expression, your chart dimensions))

sum(aggr(F4, F1,F2))

Suerte

400 Views

Anonymous

Not applicable

2010-12-09
12:07 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

400 Views

johnw

Champion III

2010-12-10
03:27 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

I'd think this?

sum(aggr(sum(F4),F1,F2))

If there is more than one value for F4 for a given combination of F1 and F2, merely saying F4 would return null. You want the sum, so you'd want sum(F4) inside the aggr(), I'd think.

400 Views