Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
eldadpaz
Partner - Contributor
Partner - Contributor

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
Creator
Creator

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
)

eldadpaz
Partner - Contributor
Partner - Contributor
Author

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.