Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL:
**Learn More**

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Problem with if-request

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Not applicable

2012-12-25
04:23 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Problem with if-request

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

441 Views

0 Replies