topic Re: conditional sum ignoring dimension in straight table in QlikView

conditional sum ignoring dimension in straight table

Mon, 08 Jun 2015 03:01:53 GMT

Hi,

How can I sum parent entity sales in a column within a straight table, and not be restricted by dimension?

For example, let's say my data looks like this:

Salesperson | Sales | Salesteam

John | 5 | Red team

Chris | 5 | Red team

Fred | 5 | Blue team

I want to create a straight table where salesperson is dimension, sum(sales) is expression, and the sum(sales) for that salesperson's entire team is also listed as an expression (this is my "parent entity sales total" column). Currently it restricts the 'Salesteam' column sum to include only those records that contain the salesperson that is listed for that specific row, i.e., how can I make qlikview sum the total for a particular person's salesteam over ALL salespeople (and ignore the specific salesperson dimension/straight table row)

Based on above data, my goal is to have this output:

Salesperson | Sales | Salesteam total

John | 5 | 10

Chris | 5 | 10

Fred | 5 | 5

In the above, '10' is the Red team total, '5' is the Blue team total.

Any guidance would be greatly appreciated - thx!

Re: conditional sum ignoring dimension in straight table

Gysbert_Wassenaar — Mon, 08 Jun 2015 15:20:07 GMT

Use sum(total <Salesteam>) for the salesteam total.

Re: conditional sum ignoring dimension in straight table

Tue, 15 Dec 2015 23:15:12 GMT

Hi,

May I add the next Level of complexity?

I also have a similar Problem with two dimensions in a straight table. I want to calculate the count of "Rotations per Station" disregarding the dimension "Aircraft". Count of "Rotatons per Station" is the sum of "Rotations". Rotations itself is the count of records. The base data consist of in total 331 lines with fields station, aircraft.

For Rotations per Station I used: count(total Station)

However this Returns 331 in each line for "Rotations per Station".

What Expression is needed to get the result for "Rotations per Station" shown below?

Station	Aircraft	Rotations	Rotations per Station
ATL	A343	52	83
ATL	A346	26	83
ATL	A330	5	83
JFK	B748	85	248
JFK	A346	70	248
JFK	A380	52	248
JFK	A330	41	248

Brgds

Ralf

Re: conditional sum ignoring dimension in straight table

Gysbert_Wassenaar — Wed, 16 Dec 2015 16:11:30 GMT

May I add the next Level of complexity?

Sure, but not in this discussion. Start a new topic for a new question please.