Chart Pivot Table, how to add bacis calculations?

[#1]

Hi everyone,

I would like to ask for an advice. I have a chart pivot table, where

part of the columns show number of passengers and bus stops. I need to

show total number of the passengers in the bus after each stop.

The math should be simple. Example, at first stop 245 people came in.

Next, stop +6 (in) and -15 (out).

  245+6-15=236 people are in the bus after 2nd stop.

Looks simple, but I cannot integrate this calculation into Pivot Table.

Question is, how can it be done? My data source is SQL file.

(Maybe to use chart pivot table is not the best option….?)

Appreciate your assistance.

Example of data table is below.

Name	NameID	Time	Stop	Came (In)	Left (out)	Is in bus
Total				173	173	-
149	149-01	Total		114	114	-
149	149-01	07:00	Stop1	25	0	25
149	149-01	07:26	Stop2	6	15	16
149	149-01	07:43	Stop3	4	10	10
149	149-01	07:55	Stop4	9	0	19
149	149-01	08:15	Stop5	70	69	20
149	149-01	08:32	Stop6	0	5	15
149	149-01	08:45	Stop7	0	15	0
149	149-02	Total		59	59	-
149	149-02	20:11	Stop1	30	0	30
149	149-02	20:35	Stop2	13	5	38
149	149-02	20:50	Stop3	1	7	32
149	149-02	21:02	Stop4	0	9	23
149	149-02	21:19	Stop5	14	0	37
149	149-02	21:30	Stop6	1	24	14
149	149-02	21:45	Stop7	0	14	0

[ecolomer]

Try this formula:

InBus=above(InBus)+CameIn-LeftOut

[ToniKautto]

The nature of your data is like transactions. From that I would suggest remodeling the loaded data so that it gives you the people in and out of the bus as transactions. People going in are positive values, and people going out are negative values.

In the attached example I have used the CrossTable function to reorganize the data so that there is one field representing the change and one field showing the direction. The people leaving the bus are negative value by multiplying with -1.

One way to do the calculation in the chart is to do a range sum, which gives the accumulated sum over a specified range. By sorting the chart by the Stops, the range order can be found with the RowNo() function. The accumulated range is picked with the above function.

The In and Out values can be found by using a set expression targeting the specified direction. To make the Out value positive I use the fabs() function.

[#4]

Hi Toni,

Thank you, but I have a problem to apply your suggestion, as my data is coming from SQL and excel files.

Any other ideas how can I get the number of passengers if I get row numbers 1,1,1,1 (and not like 1,2,3,4,)?

Here is what I have:

Main data source is from  SQL faile.

Name	NameID	Departure	From	To	Passengers
149	149-01	1.04.2015	Stop1	Stop4	1
149	149-01	1.04.2015	Stop2	Stop4	1
…

Secondary data source is from Excel:

Stop	Time
stop1	7:00
stop2	7:26
stop3	7:43
stop4	7:55
stop5	8:15
stop6	8:32
stop7	8:45

Pivot Table:

Name	NameID	Time	Stop	In = sum(If([Stop]=[From],Passengers))	Out = sum(If([Stop]=[From],Passengers))	RowNo()	Is  In Bus
149	149-01	07:00	Stop1	25	0	1	?
149	149-01	07:26	Stop2	6	15	1	?
149	149-01	07:43	Stop3	4	10	1	?
149	149-01	07:55	Stop4	9	0	1	?
149	149-01	08:15	Stop5	70	69	1	?
149	149-01	08:32	Stop6	0	5	1	?
149	149-01	08:45	Stop7	0	15	1	?

How to calculate RowNo()?

Now I have all “1” instead of 1,2,3…

Any ideas?

  Thanks

[ToniKautto]

I would still argue that you need to model the data properly to match your requirements. It does not really matter if you have data source or multiple, you can still accomplish the same data model, it will just require that you have to work a bit more with the data.

My suggestions is that you aim to compose a table like the T1 table in my previous example, so that you have a efficient and easy to use data structure. This will give you better performance and make expressions simpler to write, understand and maintain.

JOIN and CONCATENATE are often useful features to merge tables together. I would suggest that you read up on how they can be used. If you can not sort it out, I would suggest that you create a new community question on how to combine the data you have, so that this thread only has one topic.