Get value of variable with condition

[reporting_neu]

Hi,

I would like to include a variable value ("vService" via input box) in a calculation. However, the variable should only be used if a condition is met.

I wrote the formula as follows:

...
+
Sum({$<VSM_HandlingType.HandlingType = {80,81,82,83,84}, VST_Call.xCallart = {11,12}>} vService)

The value of the variable should only be added. I used "Sum", but I don't know if that's right.

I have the problem that the value is multiplied for some data. I haven't been able to find out why yet.

For example, if I enter 10 as a variable, 10 should be added to all data with the conditions from the formula. But for some, 20 or even 70 are added. Actually, I don't need a sum, but I don't know how to add the variable with conditions without "Sum".

Can you help me please?

[diegozecchini]

Hi
In Qlik, the issue seems to be related to how the variable vService is being aggregated and applied in the calculation. When you use Sum() with a variable, Qlik applies the aggregation across all relevant data points, which may lead to unexpected results like multiplication or inflated values.

Try to use a conditional expression, instead of using Sum(), you can directly apply the variable with a conditional check. Make sure the variable is applied as a single value, not aggregated over multiple data points.
You can use an If() statement or a Set Analysis expression to include the variable value conditionally.

something like this:
Sum({$<VSM_HandlingType.HandlingType = {80,81,82,83,84}, VST_Call.xCallart = {11,12}>} Value)
+
If(Only({$<VSM_HandlingType.HandlingType = {80,81,82,83,84}, VST_Call.xCallart = {11,12}>} 1), vService)

Only(...) ensures the condition is applied at the correct granularity.

[reporting_neu]

Thank you for your answer.

Unfortunately, I don't get any values ​​(null) with the if condition. If I use "Only", the value is calculated correctly for all data that fulfill this condition. BUT: For all other data, only "null" is output. It also does not sum the values ​​from vService in the total of the table header. The value is only added once.🤔

[diegozecchini]

To fix this, is it ok to to ensure that a default value (like 0) is output when the condition is not met?

if yes you can modify the formula like this:

Sum({$<VSM_HandlingType.HandlingType = {80,81,82,83,84}, VST_Call.xCallart = {11,12}>} Value) +
If(
Count({$<VSM_HandlingType.HandlingType = {80,81,82,83,84}, VST_Call.xCallart = {11,12}>} 1) > 0,
$(vService),
0
)

[reporting_neu]

"Count" was a good idea 👍 It makes the whole thing look better.

I still have problems with the totals.

The first column is the raw values.

In the second column, the value from vService (€10) should be added. This works. But the total at the top of the table only adds €10 once, even though the value has increased by a total of €40 - 1.453,81 €.

The third column contains the value from vService. Here too, the sum is not calculated correctly.

[diegozecchini · Accepted solution]

the issue lies in how Qlik calculates totals for the table. Qlik uses expression totals by default, which evaluate the formula across all data rather than summing up the row-level results. This is why the total in the second and third columns appears incorrect.

For the second column, where you are adding vService to the raw values use Aggr() to enforce row-level logic for the totals:

Sum(
{$<VSM_HandlingType.HandlingType = {80,81,82,83,84}, VST_Call.xCallart = {11,12}>}
Value
) +
Sum(
Aggr(
$(vService),
Dimension1, Dimension2
)
)

For the third column, which displays only the value of vService for rows that meet the condition use Aggr() similarly:

Sum(
Aggr(
If(
Count({$<VSM_HandlingType.HandlingType = {80,81,82,83,84}, VST_Call.xCallart = {11,12}>} 1) > 0,
$(vService),
0
),
Dimension1, Dimension2
)
)

In the table properties, ensure that Qlik calculates the totals as Sum of Rows rather than evaluating the entire expression at once

-Go to the table's properties.
-Navigate to the Expression Settings for each column.
-Look for the Totals Calculation option and set it to Sum of Rows.

This "should" forces Qlik to sum up the row-level values, ensuring accurate totals.

[reporting_neu]

Great! Thank you very much! That works. I added the condition so that when all the amounts are added together the correct total is displayed.
Thank you very much!

...

+

Sum({$<VSM_HandlingType.HandlingType = {80,81,82,83,84}, VST_Call.xCallart = {11,12}>}
Aggr(
$(vService),
VST_CallHandling.Callnumber
)
)

[diegozecchini]

Great! I am glad to know it