How to: Create P&L chart

Blank

0

Line

1

Subtotal

2

Total

3

3

Total

2

SubTotal

1

Line

0

Blank

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

*

*

*

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

ID

PandLPercent.LineIDPercent

PandLPercent

PandL

PandLPercent.Keyfigure Percent

PandLPercent

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))))

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.

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.

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