Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
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
)

Highlighted
Partner
Partner

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.