Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

Pivot table sum

Hi,

I'm trying to make a table showing the amount of money we save for our customer, by showing the amount of money they spent on a lane (from a certain city to another city)  in 2012, and what they are spending now we are arranging their transports.

So the table has the following content:

Average paid rate 2012: what the customer paid in 2012, before we arranged their transport

Average freight spend SWL: what we the customer paid since we arranged their transports

# of loads: number of loads we shipped for them on the specific lane.

Savings by lane.bmp

My data comes from two datasets; one set with all the shipment data from my company, and one file with the 2012 rates of the customer. I have Flagged our own data (because there are also many 2012 lanes in the file which are not relevant anymore), and made the connection between the files based on "Lane".

In the outer right column, I want to show what the customer would have paid on these lanes if they would have arranged their transports themselves. I do this, by multiplying their rate, with the number of loads shipped. Where there is no data

=if(sum({<FlagA={'A'}>}TotalFreight)=0

,0,Avg({<FlagA={'A'}>}[Rates 2012 in Eur])*

count({<FlagA={'A'}={0}>}TMWO))

As you can see in the picture, this formula works perfectly for the separate lanes, but my totals get all messed up. Can anybody tell me, how I can get the same numbers per lane, but the correct total? (=3144+3110,40+900+294=7418,4)

Many thanks!

Tags (2)
1 Solution

Accepted Solutions

Re: Pivot table sum

Hi,

If you are using straight table .

go to chart Properties->Expression->select Expression->Total Mode->select (Sum) of Rows.

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
4 Replies
MVP & Luminary
MVP & Luminary

Re: Pivot table sum

Perhaps an extra aggregation over the dimensions will help:

=sum(aggr(

if(sum({<FlagA={'A'}>}TotalFreight)=0

,0,Avg({<FlagA={'A'}>}[Rates 2012 in Eur])*

count({<FlagA={'A'}={0}>}TMWO))

, ConsigneeCount...something... , Lane)


talk is cheap, supply exceeds demand

Re: Pivot table sum

Hi,

If you are using straight table .

go to chart Properties->Expression->select Expression->Total Mode->select (Sum) of Rows.

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Not applicable

Re: Pivot table sum

Yeah, that worked. Thank you Max!

Re: Pivot table sum

You Welcome,

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.