Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

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
4 Replies
ecolomer
Honored Contributor II

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

Try this formula:

InBus=above(InBus)+CameIn-LeftOut

Employee
Employee

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

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.

Not applicable

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

Employee
Employee

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

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.

Community Browser