Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Partner
Partner

P&L Issue with variable targets

Hi there,

I am trying to build a P&L which has several calculated targets, based on actual income (e.g. FoodCost's target should be 42% of actual income).

I've managed to get all targets value to appear correctly for ExpenseType=(Income,Fixed), however, I can't manage to get the variable expenses to show correctly.

For the Target calculation I tried this:

If (
TargetType='Absoloute',
Sum(TargetValue),
Sum(TargetValue)*Sum({<Expense={'Income'}>}ActualValue)*-1
)

This statement results in showing 0.00 for the target value.

 

Attached are the excel

Appreciate your help

2 Replies
anseglko
New Contributor III

Re: P&L Issue with variable targets

Could it be because the first row is BOTH TargetType='Absoloute' and Expense={'Income'}, so the if-statement skips that when you try to calculate the else value "Sum(TargetValue)*Sum({<Expense={'Income'}>}ActualValue)*-1"?

 

What happens if you try

If (
TargetType='Absoloute' and Expense<>'Income',
Sum(TargetValue),
Sum(TargetValue)*Sum({<Expense={'Income'}>}ActualValue)*-1
)

Partner
Partner

Re: P&L Issue with variable targets

Thanks, Anseglko, but it didn't work.

My relative target for Expense=FoodCost is 42% of actual income for each month.

So, if the actual income in March is 65,340$, the FoodCost target for that month should be: 27,442$. This calculation should be done for each month which has actual income. 

Please notice that:

1. The relative targets are only for Variable Expenses and not for Fixed Expenses 

2. For target variable expenses ONLY, I am trying to multiply the TargetValue of that expense (e.g. 0.42) by the ActualValue (e.g 65,340$) of a different expense (i.e. Income)

I can't figure out why the multiplication is not done and results in 0 value.