Profit and loss statement with muliplication and divisions

[#1]

Hi All

in The Below statement I want to calculate one extra P&L Heading  Sales-Dis it is equal to  GrossSales/Off Invoice Discounts

and also Multiplication of these two fields. GrossSales*Off Invoice Discounts  as another P&L Heading

how do I calculate these. Please help me on this.

Best Regards

John

[miguelbraga]

Hey John,

In the load script you need to make something like this:

YourNewTable:

LOAD      *,

               [Gross Sales]/[Off Invoice Discounts] as [Extra P&L Heading One],

               [Gross Sales]*[Off Invoice Discounts] as [Extra P&L Heading Two]

Resident YourTable;

Drop Table YourTable;

Hope it helps

[#3]

Hi Braga Miguel , sorry if made you confuse , or if my statements are not clear ,  I want these two as field values in the Field P&L Heading

Regards

John

[miguelbraga]

There is a way to calculate those 2 extra P&L Healing with set analysis like this:

extra1:

=sum({<[P&L Heading] = {[Gross Sales]}, Year = {'2011'}}> Actual) / sum({<[P&L Heading] = {[Off Invoice Discounts]}, Year = {'2011'}}> Actual)

extra2:

=sum({<[P&L Heading] = {[Gross Sales]}, Year = {'2011'}}> Actual) * sum({<[P&L Heading] = {[Off Invoice Discounts]}, Year = {'2011'}}> Actual)

But if you want to introduce those values inside the [P&L Heading] you need to do a preceding load in your table like this:

YourTable:

LOAD      *,

               'extra2' as [P&L Heading],

               [Gross Sales]*[Off Invoice Discounts] as Actuals // For extra2

LOAD      *,

               'extra1' as [P&L Heading],

               [Gross Sales]/[Off Invoice Discounts] as Actuals // For extra1

LOAD      *,

FROM (file);

[#5]

I would like to calculate those two Values at the time of data modeling not in set analysis. also in one table we can't have two fields with same name Qv will through an error, please correct me if I am wrong.

Regards

John

[ecolomer]

See this doc:

How to Create a Profit and Loss Statement in QlikView

[#7]

Thanks Enrique Colomer, The image I have added above has taken from the Same Application that your referring to, in that I found the calculations only with  plus , Minus , how do I do when there are multiplication and divisions are there in the P&L heading field value calculations?

Regards

John

[tresesco]

I had answered for a similar requirement and not finding that now. However, similar solution is here :Re: Multiply (or divide) two values from the same dimension in load script

[#9]

Thanks Tresesco , your always making my life easy,

here I have a problem with the expression  sum(sales)/alt(above(sum(sales),4),0) , this expression gives me the below chart its fine.

and I am using this expression to find the Variance between the Current Quarter and Previous year Same quarter for all years.however when I select the  Year it is not giving me any values. what I sthe wrong in it.

Best Regards

John