topic Re: Financial ratios in QlikView

Financial ratios

Anonymous — Tue, 03 Jan 2017 09:58:47 GMT

Hi,

I have created the following bucket:

Bucket:
Load * Inline [Bucket, Start,End
'Sales', 3000 ,3999
'Cost of services sold', 4000 ,4999
'Fixed costs', 5000 ,7699
'Other operating expenses', 7900, 7999
'Depreciations', 7800, 7899
'Financial costs', 8300, 8499
];

IntervalMatch (Account) load Start, End resident Bucket;

How can I enter the following summations/ financial ratios to the bucket?

-Gross profit

-EBITDA

-EBIT

-EBT

Thanks!

Re: Financial ratios

simotrab — Tue, 03 Jan 2017 11:13:05 GMT

Hi,

first of all the code does not work: I think something is missing, please look here:

http://help.qlik.com/en-us/sense/3.1/Subsystems/Hub/Content/Scripting/ScriptPrefixes/IntervalMatch.htm

However it seems you need the difference between Start and End, and I managed it in a simple way, maybe not correct for your purpose, because I do not understand your code:

Bucket:

Load

Bucket,

Start,

End,

End-Start as Difference

Inline [

Bucket, Start,End

'Sales', 3000 ,3999

'Cost of services sold', 4000 ,4999

'Fixed costs', 5000 ,7699

'Other operating expenses', 7900, 7999

'Depreciations', 7800, 7899

'Financial costs', 8300, 8499

];

Now the formulae, I think is something like

GROSS PROFIT

Gross profit - Wikipedia

so something like

sum({$<Bucket={"Sales"}>}Difference)

-sum({$<Bucket={"Cost of services sold"}>}Difference)

EBIT

Earnings before interest and taxes - Wikipedia

sum({$<Bucket={"..."}>}Difference)

-sum({$<Bucket={"..."}>}Difference)

...

-sum({$<Bucket={"..."}>}Difference)

Putting on the points in the set analysis the correct name of the field Bucket, and adding or subtract everything needed.

And so on.

I am not an accounter, so maybe my formulae are semantically wrong, but the logic behind is this, use set analysis to reach the different results.

For more info:

Set Analysis: syntaxes, examples

Hope to be helpful

Re: Financial ratios

SteveNewman — Tue, 03 Jan 2017 13:58:02 GMT

‌You may want to look at this example that I have used. It is a work in progress and if you have anything else to contribute or improve, please let me know.

GitHub - newmans99/Qlik-Financial-Rollup-Example

Steve