4 Replies Latest reply: Apr 14, 2015 6:47 PM by Toni Kautto RSS

    Chart Pivot Table, how to add bacis calculations?

      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.

       

             

      NameNameIDTimeStopCame (In)Left (out)Is in bus
      Total 173173-
      149149-01Total 114114-
      149149-0107:00Stop125025
      149149-0107:26Stop261516
      149149-0107:43Stop341010
      149149-0107:55Stop49019
      149149-0108:15Stop5706920
      149149-0108:32Stop60515
      149149-0108:45Stop70150
      149149-02Total 5959-
      149149-0220:11Stop130030
      149149-0220:35Stop213538
      149149-0220:50Stop31732
      149149-0221:02Stop40923
      149149-0221:19Stop514037
      149149-0221:30Stop612414
      149149-0221:45Stop70140
        • Re: Chart Pivot Table, how to add bacis calculations?
          Enrique Colomer

          Try this formula:

          InBus=above(InBus)+CameIn-LeftOut

          • Re: Chart Pivot Table, how to add bacis calculations?
            Toni Kautto

            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.

              • Re: Chart Pivot Table, how to add bacis calculations?

                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. 1.04.2015

                Stop1

                Stop4

                1

                149

                149-01

                1. 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

                  • Re: Chart Pivot Table, how to add bacis calculations?
                    Toni Kautto

                    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.