Create income statement table with common size percentage

[katetsan]

Hi all,

I have a set of raw data with format as below <table 1>. I'm wondering if there's anyway to create an income statement table with common size percentage as below <table 2>.

Thanks a lot!

<Table 1>Raw data format

Item	Revenues	Costs	Expense
A	90	20	8
B	100	25	9
C	110	35	11

<Table 2> Income statement table with common size%

	Number	Header 3
Revenues	300	100%
Costs	80	26.7%
Gross	220	73.3%
Expense	28	9.3%
Profit	192	64%

[devarasu07]

Hi,

Try to use cross table method,

Data:

CrossTable(Type, Amount)

LOAD * INLINE [

    Item, Revenues, Costs, Expense

    A, 90, 20, 8

    B, 100, 25, 9

    C, 110, 35, 11

];

[katetsan]

Hi Devarasu,

Thanks for your reply.

However, there're over 1000 rows of data flow, it's hard to use load inline scripts.

In addition, the data flow is like the list of transaction without summary.

Is there any alternative suggestion?

Thanks a lot!

[devarasu07]

Hi

You can use excel/other data source for your live report. using cross table method it should n off rows. give a try and let us know if any issue. Thanks

[sunny_talwar]

May be check out this

How to Create a Profit and Loss Statement in QlikView‌

How IntervalMatch Solved My Profit and Loss Dilemma

[effinty2112]

Hi Kate,

You can get a straight table like this:

Category	Number	Header3
Revenues	300	100%
Costs	80	26.7%
Gross	220	73.3%
Expense	28	9.3%
Profit	192	64.0%

The dimension Category is a calculated dimension:

=ValueList('Number','Header3')

These are the expressions:

Revenues:

Pick(Match(ValueList('Number','Header3'),'Number','Header3'),

Sum(Revenues),num(1,'0%'))

Costs:

Pick(Match(ValueList('Number','Header3'),'Number','Header3'),

Sum(Costs),num(Sum(Costs)/ Sum(Revenues),'0.0%'))

Gross:

Pick(Match(ValueList('Number','Header3'),'Number','Header3'),

Sum(Revenues)-Sum(Costs),num((Sum(Revenues)-Sum(Costs))/ Sum(Revenues),'0.0%'))

Expense:

Pick(Match(ValueList('Number','Header3'),'Number','Header3'),

Sum(Expense),num(Sum(Expense)/ Sum(Revenues),'0.0%'))

Profit:

Pick(Match(ValueList('Number','Header3'),'Number','Header3'),

Sum(Revenues)-Sum(Costs)-Sum(Expense),num((Sum(Revenues)-Sum(Costs)-Sum(Expense))/ Sum(Revenues),'0.0%'))

In the Presentation tab of the straight table check the Horizontal box.

good luck

Andrew

[Kushal_Chawda · Accepted solution]

Create one inline table in script

1st method:

LOAD * inline [

Dim

1

2

3

4

5 ];

create the straight table

Dimension:

Pick(Dim,'Revenues','Costs','Gross','Expense','Profit')

Expression:

1) Number :

Pick(Dim,

Sum(Revenues),

Sum(Costs),

Sum(Revenues)-Sum(Costs),

Sum(Expense),

Sum(Revenues)-Sum(Costs)-Sum(Expense))

2) %

Pick(Dim,

Sum(Revenues)/Sum(Revenues),

Sum(Costs)/Sum(Revenues),

(Sum(Revenues)-Sum(Costs))/Sum(Revenues),

Sum(Expense)/Sum(Revenues),

(Sum(Revenues)-Sum(Costs)-Sum(Expense))/Sum(Revenues))

2nd method:

create the straight table

Dimension:

Valuelist('Revenues','Costs','Gross','Expense','Profit')

Expression:

1) Number :

Pick(match(Valuelist('Revenues','Costs','Gross','Expense','Profit'),'Revenues','Costs','Gross','Expense','Profit'),

Sum(Revenues),

Sum(Costs),

Sum(Revenues)-Sum(Costs),

Sum(Expense),

Sum(Revenues)-Sum(Costs)-Sum(Expense))

2) %

Pick(match(Valuelist('Revenues','Costs','Gross','Expense','Profit'),'Revenues','Costs','Gross','Expense','Profit'),

Sum(Revenues)/Sum(Revenues),

Sum(Costs)/Sum(Revenues),

(Sum(Revenues)-Sum(Costs))/Sum(Revenues),

Sum(Expense)/Sum(Revenues),

(Sum(Revenues)-Sum(Costs)-Sum(Expense))/Sum(Revenues))

Note: for % expression, go to chart properties->Number tab -> check Fixed to 1 and check show in % option

Update: Updated % expression

[Kushal_Chawda]

Updated expression in my OP

[katetsan]

Hi Kushal,

It works perfectly. Thanks a lot!

[katetsan]

Hi Andrew,

It works !! Thanks A LOT!!!