trueChart Help

How to create a P&L chart

This is an example for a Profit and Loss Statement. It contains a trueChart object which consists of a grid and several tables with waterfall charts and values.

Prepare the layout

01

Add a new trueChart object and switch to the Layout editor.

02

In the root cell click on Grid.

03
04

Adjust the width of columns A and B. Setting for column A:

05

Setting for column B

06

Add five rows in the cell A1 (Click on Row Settings → Add row)

07

Configure row 1 (A1)(Row Settings) to be always 60 pixels high. This row is to contain titles and information about the visualizations .

08

Configure row 2 (A2). Note the Content-based optimization and the minimum height of 300 pixels

09

Configure row 3 (A3). This row is used as a blank row, used as a separator between visualizations.

10

Configure row 4. (A4) This row is used to store titles/information about the visualization in the cell underneath

11

Configure row 5.

12

Click in Cell A5 and click the Grid Button. This will divide the cell A5 in 4 equal cells.

13

Adjust the height of newly created rows

14

Row 1

15

Row 2

16

Place the cursor in the cell A1.A5.A2

17

Click on InitialGrid

18

This divides the cell into 4 equal subcells

19

Place the cursor in the cell A1.A1 → Intial → „Grid“

20

Delete Row 2.

21

Settings for the newly created column A and B

22

Place the cursor in the newly created cell B → InitialGrid

23

Row settings

24

Column settings

25

Now format the grid in the cell A1.1.A1

26

Place the cursor in the newly created cell A1.A1.A1 → InitialGrid Then delete row 2 of the newly created cell. Format the remaining row 1 at 70 pixels height.

27

In cell A1. A1. A1.B1 click on InitialGrid

28

Delete row 2

29
30

In cell A1. A1. A1.B1.B1 click on InitialGrid

31

Delete Row 2

32

Format column A to have weight 2

33

Format column A to have weight 1

34

Place the cursor in cell A1.A4 → InitialGrid, delete row 2

A1.A4.B1, InitialGrid

35

Format newly created rows to have the same weight

36

Layout is finished

37

trueChart settings

The upper table contains the absolute values, the bottom table contains values in percent.

InitialSubtitleEdit Cell

38

If Edit is set to Always, it is possible to enter text in the cell.

39

Table configuration

40

Cell type Table

41

Repeat Notation repeats the header notation at the botton of the table. Untick the Show header option.

42

Keyfigure tab of the cell menu.

FlagLineType is a hidden Keyfigure.

43

Description tab of the cell menu.

44

Input tab

45
46
47

Right click on table → Data to enter the trueChart context

48

Set the Data Rules for the Keyfigure AC - there are three data rules, one for Line Type "Total", the second for Line Type "Subtotal" and the third one for "Line". FlagLineType is a Keyfigure that is built upon the field PandL.LineType.

PandL.LineType

FlagLineType

Blank

0

Line

1

Subtotal

2

Total

3

49

„FlagLineType“ is the second measure in the BI context, the position of the measure is referred to in trueChart condition.

50

The values are set as "Total", "Subtotal" and "Normal" to match condition. tC will then render the Lines with FlagLineType 3 as a Total, 2 as a Subtotal and 1 as a Line. Here are "Revenue" and "Total Variable Cost" Subtotals, "Contribution Margin" is a Total, Lines 1,2,6,7,8 are Lines. 5 is a Blank.

51
52
53
54

Go to context and assign the Scaling Group "abs" to the keyfigure "AC".

55

Base Settings for Keyfigure "PY" ist formatted as Past and Actual, since it contains the actual figures from the previous year.

56
57

Add a scaling group for ΔPY.

58

Define a data rule for ΔPY and ΔFC. This rule applies to all lines except for blank (which have ID 5,11 and 16).

59

Settings for measure FC

60

Measure FC

61

Measure Rating

62

Comment

Condition $(K02)<>false means that the rule does not apply to measure FlagLineType = 0 which is a blank line.

FlagLineType PandL.LineType

3

Total

2

SubTotal

1

Line

0

Blank

63

CellTypeTable dialog

65

Table Settings

ID is hidden measure

66

Secondary table

Choose TableEdit Cell

67

Note that Axis mid places the axis that divides negative and positive values in the middle of the cell

68

Settings in the Description tab

69

No Outlier settings

70

Go to the Data menu, no settings for dimensions were made

71

PY measure

Add % units for each of conditions, scaling as 0.01 is equivalent to multiplying the figure by 100

For each condition „Total“, Subtotal“ and „Normal“ is set as Value.

72

Measure AC

73

Measure AC

Activate Reference toggle. References can be used to viszualize the delta according the base. If set to active, the values for Time, Type, Version, Delta and Individual are used as a reference for keyfigures set as delta. This reference may be shown on the axis of the cell.

74

FC measure

75

ΔPY %P

76

FC %P measure

When Delta is ticked on, the figures will be colored red (negative) and green (positive), if the concept Color or Area-Color is chosen in cell menu.

77

Rating measure

No settings in this screen, definition as measure in cell menu.

78

Comment measure

No settings in this screen, definition as measure in cell menu.

79

Right Click → Table

80

Hide the ID column.

The Use available space setting spans the entire table to the maximum width of the cell where it is placed.

81

Adjust titles and blank cells

Format cells as buttons, see example below.

82

Click in the Cell A1.A1.A1 and click on Edit Cell and choose the cell type as Button

83
84

Configure the button: Set Content Alignment, Horizontal to (left-aligned).

85

Set Weight to Bold and Size to 14.

86

Cell A1.A2.A1: Open the Cell Menu and chosse Button as cell type.

87

Visualization → Layout

88

StyleBackground: activate the Background toggle

89

Format cell as Blank

90

BI contexts

Two BI contexts must be created, one for the table with absolute values (PandLStructure) and one for the table with percent values (PandLPercent).

PandLStructure

Dimension name

Field

Table

Explanation

ID

PandL.LineID

PandL

This is a hidden dimension, used for sorting lines in PandL. Contains valuen [1,..20]. PandL.LineID has a description assigned to it in the key figure PandL.Keyfigure.

PandL

PandL.Keyfigure

PandL

This is the description of the key figure. 91

*

*

*

The setting "Include Zero Values" has to be unticked, since there are other PandL.Keyfigure values that are not to be evaluated as dimensions. 92 93

Measures

Name Dynamic label Definition Explanation

PYA

PA

Sum(
 {<
  [Calendar.Year]  =,
  [Calendar.Month] =,
  [Calendar.ReportingMonth] =,
  [Calendar.BeginOfMonthNum]=
    {">=42736<=42948"},
  [Facts.Datetype]={'ACT'},
  [PandL.StructureType]={'Reduced'}
 >}
[Facts.Value]) / 1000

The sum over the Field Facts.Value of the Facts table, restricted through a set expression. Selections for Year, Month and ReportingMonth are ignored >=01.01.2017⇐01.08.2017 if "Aug 2018" ist selected in menubar Facts.Datatype is filetered to 'ACT'.

FlagLineType

only(
 {<
  [Facts.Datetype]={'ACT'},
  [PandL.StructureType]={'Reduced'}
 >}
 if(PandL.LineType='Line', 1,
 if(PandL.LineType='SubTotal', 2,
 if(PandL.LineType='Total', 3, 0
 )))
)

This keyfigure is used in the tC condition to define Title, Subtitle or Line format for table lines.

  • Evaluates to 1 if the field PandL.LineType is "Line",

  • Evaluates to 2 if the field PandL.LineType is "SubTotal",

  • Evaluates to 3 if the field PandL.LineType is "Total",

  • Evaluates to 0 if the field PandL.LineType is "Blank".

CYA

AC

Sum(
  {<
  [Calendar.Year]  =,
  [Calendar.Month] =,
  [Calendar.ReportingMonth] =,
  [Calendar.BeginOfMonthNum]=
    {">=43101<=43313"},
  [Facts.Datetype]={'ACT'},
  [PandL.StructureType]={'Reduced'}
  >} [Facts.Value]) / 1000

Actual Sales

FC

Sum(
 {<
  [Calendar.Year]  =,
  [Calendar.Month] =,
  [Calendar.ReportingMonth] =,
  [Calendar.BeginOfMonthNum]=
    {">=43101<=43313"},
  [Facts.Datetype]={'FC1'},
  [PandL.StructureType]={'Reduced'}
 >} [Facts.Value]) / 1000

Sales Forecast Keyfigure

ΔPY

Column(3)-Column(1)

this corresponds to ΔPY = AC - PY The column number is counted according to the order of the Keyfigures in the Qlik Sense Data Context

ΔPY%

(Column(3)-Column(1))/Column(3)

ΔPY = (AC - PY)/AC

ΔFC

Column(3)-Column(4)

AC – FC

Rating

='Rating'

'tclt:11000tc;;Keytc$$Very goodtc§#VerygoodKeytc§§Goodtc§#GoodKeytc§§Acceptabletc§#AcceptableKeytc§§Badtc§#BadKeytc§§Criticaltc§#CriticalKeytc§§'

This is html code for a drop-down menu of options for commenting

Comment

='Comment'

1

This measure is added with value 1, because it is to be set as "comment" in the cell menu; it does not contain key figure definition, but has to be added in the BI context

PandLPercent

Dimension name Field Table Explanation

ID

PandLPercent.LineIDPercent

PandLPercent

This is a hidden dimension, used for sorting lines in PandL. Contains valuen [6,5,..20,5]. PandL.LineID has a description assigned to it in the key figure 94 PandLPercent.KeyfigurePercent

PandL

PandLPercent.Keyfigure Percent

PandLPercent

Measures

Name Dynamic label Definition Explanation

PYA

PY

Sum(
{<
[Calendar.Year]  =,
[Calendar.Month] =,
[Calendar.ReportingMonth] =,

[Calendar.BeginOfMonthNum]={">=42736<=42948"},
[Facts.Datetype]={'ACT'},
[PandLPercent.StructureTypePercent]={'Extended'},
[PandLPercent.flagPercentOfRevenue]={'inPercent'},
[PandLPercent.flagTotalRevenue]=
>}
[Facts.Value])
/
Sum(
{<
[Calendar.Year]  =,
[Calendar.Month] =,
[Calendar.ReportingMonth] =,

[Calendar.BeginOfMonthNum]={">=42736<=42948"},
[Facts.Datetype]={'ACT'},

[PandLPercent.StructureTypePercent]={'Extended'},
[PandLPercent.flagPercentOfRevenue]=,
[PandLPercent.flagTotalRevenue]={'TotalRevenue'}
>}
[Facts.Value])

ignore selections for Year, Month, and ReportingMonth >=01.01.2017⇐01.08.2017"

CYA

AC

Sum(
{<
[Calendar.Year]  =,
[Calendar.Month] =,
[Calendar.ReportingMonth] =,

[Calendar.BeginOfMonthNum]={">=43101<=43313"},
[Facts.Datetype]={'ACT'},
[PandLPercent.StructureTypePercent]={'Extended'},
[PandLPercent.flagPercentOfRevenue]={'inPercent'},
[PandLPercent.flagTotalRevenue]=
>}
[Facts.Value])
/
Sum(
{<
[Calendar.Year]  =,
[Calendar.Month] =,
[Calendar.ReportingMonth] =,

[Calendar.BeginOfMonthNum]={">=43101<=43313"},
[Facts.Datetype]={'ACT'},
[PandLPercent.StructureTypePercent]={'Extended'},
[PandLPercent.flagPercentOfRevenue]=,
[PandLPercent.flagTotalRevenue]={'TotalRevenue'}
>}
[Facts.Value])

">=01.01.2018⇐01.08.2018"

FC

Sum(
{<
[Calendar.Year]  =,
[Calendar.Month] =,
[Calendar.ReportingMonth] =,

[Calendar.BeginOfMonthNum]={">=43101<=43313"},
[Facts.Datetype]={'FC1'},
[PandLPercent.StructureTypePercent]={'Extended'},
[PandLPercent.flagPercentOfRevenue]={'inPercent'},
[PandLPercent.flagTotalRevenue]=
>}
[Facts.Value])
/
Sum(
{<
[Calendar.Year]  =,
[Calendar.Month] =,
[Calendar.ReportingMonth] =,

[Calendar.BeginOfMonthNum]={">=43101<=43313"},
[Facts.Datetype]={'FC1'},
[PandLPercent.StructureTypePercent]={'Extended'},
[PandLPercent.flagPercentOfRevenue]=,
[PandLPercent.flagTotalRevenue]={'TotalRevenue'}
>}
[Facts.Value])

ΔFC %P

column(5) - column(1)

CYA – FYA

FC %P

column(5) - column(6)

CYA - FC

Rating

='Rating'

'tclt:11000tc;;Keytc$$Very good tc§# VerygoodKeytc§§Goodtc§#GoodKeytc§§Acceptabletc §#AcceptableKeytc§§Badtc§#BadKeytc§§ Criticaltc§#CriticalKeytc§§'

Comment

= 'Comment'

1

This is a keyfigure that is created in b´BI context but is configured in trueChart context and is used as a comment field

FlagLineType

only(
{<
[Facts.Datetype]={'ACT'},
[PandLPercent.StructureTypePercent]={'Reduced'}
>}
if(PandLPercent.LineTypePercent='Line', 1, if(PandLPercent.LineTypePercent='SubTotal', 2, if(PandLPercent.LineTypePercent='Total', 3, 0))))

Basic data model explanations

PandL table

Field Explanation Screenshot

PandL.LineID

Used as a hidden dimension in the table. Values [1,..20]

%PandL.Key

This field serves as a primary key in the table PandL and connects it to the Facts table.

PandL.LineIDReduced

Contains values [1,…​10], and is used as filter for the reduced representation of PandL

PandL.Keyfigure

Contains keyfigure description.

95

PandL.LineType

Contains values [Line, Subtotal, Total, Blank]. Keyfigure FlagLineType is built upon this field, which is then used to assign data rules in tC, so that

tC interprets lines as Total, Subtotal or Normal.

PandL. IndentType

Contains values [0,1]

PandL.StructureType

Contains values [Extended, Reduced]. For each keyfigure (PandL.Keyfigure) there is an entry as ‘Extended’ and ‘Reduced’. So it needs to be restricted to either of these valued in the set expression of the keyfigure to avoid double counting.

Facts table

Field Explanation

%PandL.Key

This field serves as a primary key, it connects Facts table with tables PandL and PandLPercent

Facts.Value

Contains the values for all keyfigures

Facts.Datatype

Contains values [ACT,BUD, FC1, FC2, FC3]. Categorizes a keyfigure as actual, budget or 3 types of forecast.

%Date

Key that connects Facts table with Calendar Table. The time granularity is day.