Problem with if-request

Tue, 25 Dec 2012 09:23:09 GMT

Dear all,

First I would like to wish you and your families a Merry Christmas

I have a problem. For a Proft & Loss Report, it is neccessary to work with the data from the current year to have plandates for next year. I have the following Table:

Profit and Loss	Formula	2012	2013
Net Sales	sum(NETSALES) + Sum(PLANSALES)	1.000	1.200
Direct Cost of Sales	sum(DCOS) * -1	-450	-540
Product Margin	sum(NETSALES) + sum(DCOS)*-1	550	660
Remaining Cost of Sales	sum(RCOS) * -1	-120	-144
Gross Profit	[Product Margin] + sum(RCOS)*-1	430	516
Marketing and Selling Expenses	sum([MS Exp]) * -1	-120	-138
SG&A Expenses	sum([SGA Exp])*-1	-120	-123
R&D Expenses	sum([RD Exp]) * -1	-120	-133
EBIT	[Gross Profit] - sum([MS Exp]) - sum([SGA Exp]) - sum([RD Exp])	70	122
Taxes	MaxString(TAXES_YTD) * -1	-11	-17
Interest Expenses	MaxString(INTEREST_YTD) * -1	-18	-21
NOPAT	EBIT + Taxes + [Interest Expenses]	52	101

The numbers from year 2012 are in a Excel Sheet. With the Formula, all the results in 2012 are right. The data for the year 2013 must be calculatet. Only the Plansales for 2013 is given in the Excel Sheet.

Direct Cost of Sales: previous year * sales increase

Product Margin: Net Sales - Direct Cost of Sales

Remaining Cost of Sales: previous year * sales increase

Gross Profit: Product Margin - Remaining Cost of Sales

Marketing and Selling Expenses: previous year * sales increase/ FTE-increase

SG&A Expenses: previous year * sales increase/FTE-increase

R&D Expenses: previous year * sales increase /FTE-increase

EBIT: Gross Profit + Marketing and Selling Expenses + SG&A Expenses + R&D Expenses

Taxes: EBIT * rate of taxes * -1

Interest Expenses: Dept Capital * interest rate * -1

NOPAT: EBIT + Taxes + Interest Expenses

So I changed the formula from Direct cost of Sales to the following formula:

if(YEAR = 2012, sum(DCOS) * -1, sum(DCOS) + (sum(DCOS) * (vAMOUNT2013 - vAMOUNT2012)/100))

vAMOUNT2012 = sum({$ <YEAR = {2012}>}NETSALES)

vAMOUNT2013 = sum({$ <YEAR = {2013}>}PLANSALES)

But the formula doesn't work in the if - request. When i try only the second part of the formula in the if-request, the result for 2013 is right.

Can anybody help me, to get the rigth results for 2012 and 2013 in one formula? Or can anybody tell me why the if-request doesn't work?

Would be great to post your idea here, because i cannot open files from another person.

Best Regards,

Kerstin Sophia

topic Problem with if-request in QlikView

Problem with if-request