TRUECHART Knowledge Base Live Search

Search

Page tree

trueChart Help

Skip to end of metadata
Go to start of metadata

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


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


In the root cell click on Grid.



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


Setting for column B

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

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

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

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

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

Configure row 5.

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

Adjust the height of newly created rows

Row 1

Row 2

Place the cursor in the cell A1.A5.A2

Click on InitialGrid

This divides the cell into 4 equal subcells

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

Delete Row 2.

Settings for the newly created column A and B

Place the cursor in the newly created cell B → InitialGrid

Row settings

Column settings

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

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.

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

Delete row 2

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

Delete Row 2

Format column A to have weight 2

Format column A to have weight 1

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

A1.A4.B1, InitialGrid

Format newly created rows to have the same weight

Layout is finished

TRUECHART settings

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

InitialSubtitleEdit Cell

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

Table configuration

Cell type Table
Repeat Notation repeats the header notation at the botton of the table

Right click on table → Data to enter the TRUECHART context

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

Blank

0

Line

1

Subtotal

2

Total

3


"FlagLineType" is the second measure in the BI context, the position of the measure is referred to in TRUECHART condition.

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.




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

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

Add a scaling group for ΔPY.

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

Settings for measure FC

Measure FC

Measure Rating

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

Table Layout

ID is hidden measure


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

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.

Measure AC

Measure AC

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

FC measure

ΔPY %P

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.


Rating measure

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

Comment measure

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

Right Click → Table

Hide the ID column.

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

Adjust titles and blank cells

Format cells as buttons, see example below

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

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

Set Weight to Bold and Size to 14 .

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

Visualization → Layout

StyleBackground : activate the Background toggle

Format cell as Blank

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 nameFieldTableExplanation

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. 

*

*

*

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

Measures

NameDynamic labelDefinitionExplanation

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 nameFieldTableExplanation

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

NameDynamic labelDefinitionExplanation

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

FieldExplanationScreenshot

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

FieldExplanation

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