Announcements
cancel
Showing results for
Did you mean:
Creator III

## summation based on condition of other expressions

Hello Experts,

PFA

As per the need i need to show sum on the basis of  step1 (sum(step1)).

if step1 cell value is greater than 0.0036334529378426

then I need to do summation of all those value.

for instance in the below screenshot ,we have 2 values that are greater than 0.0036334529378426 then there will be sum of these 2 values.( I have highlighted them).
And divide total of those 2 highlighted values by count of values(2)
Can you please suggest on this.

Thanks

Labels (7)

• ### Set Analysis

1 Solution

Accepted Solutions
Employee

Hi @deep2021 ,

You can:

1. Uncheck Include null values on Dim1 column

or

2. Adding Set modifier to the formula

• Using Qlik Cloud SaaS, add {<Dim1={"*"}>} on top of formula.
• Using Qlik Enterprise Manager old versions, add {<Dim1={"*"}>} inside first SUM and COUNT expressions.

Sum({<Dim1={"*"}>} AGGR ( if ( sum(Step1)> 0.0036334529378426 , sum(Step1) ),Dim1,Dim4) ) / Count({<Dim1={"*"}>}Step1)

And take a look on this links that help you better understand how Qlik Set Analysis works:

Good Luck,

[],

Pedro

9 Replies
Specialist II

@deep2021  Could you please provide some sample data?

Also, I believe we can do this calculation at the Back end script itself. We can Flag which will 1 and 0 as a values

If sum(Step1)>0.0036334529378426 then 1 else 0

And then sum only those values which are having 1 as a flag value.

Champion III

try below

=   AVG( AGGR   ( if ( sum(Step1)> 0.0036334529378426 ,   sum(Step1) ),Dim3,Dim4) )

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Employee

I believe that you trying to get only one value for all rows and you have 346,956 lines on your table. I could not identify a column to aggregate your data, but you can apply the next formula to it.

First part will be give to you the answer to "greater than 0.0036334529378426 then there will be sum"

The second part will give to you the answer to "divide total of those 2 highlighted values by count of values(2)"

Sum({<Step1={"=Sum(Step1)>0.0036334529378426"}>}Step1) /
Count({<Step1={"=Sum(Step1)>0.0036334529378426"}>}Step1)

[],

Pedro

Creator III
Author

Hi,

I need w.r.t Dim1 and Dim4 .The above formula is not working.

Thanks

Creator III
Author

Hi Vineeth,

The formula is not working.

I need summation for all values in a column that are greater than above value and divided by  count of that values.

Thanks

Creator III
Author

Hi,

I have already provided QVF with sample data. The requirement is on the basis of expression.

We derived step1 from expression. Flags cannot be created.

Thanks

Employee

So, your expectation is get this values:

If yes, the formula provided from @vinieme12 iscorrect, just needing to add the division operation to it:

Sum( AGGR ( if ( sum(Step1)> 0.0036334529378426 , sum(Step1) ),Dim1,Dim4) ) / Count(Step1)

[],

Pedro

Creator III
Author

Hi ,

In above table summation of Dim1 = NOBEL MEADOW. We need to ignore values for nulls.

Thanks

Employee

Hi @deep2021 ,

You can:

1. Uncheck Include null values on Dim1 column

or

2. Adding Set modifier to the formula

• Using Qlik Cloud SaaS, add {<Dim1={"*"}>} on top of formula.
• Using Qlik Enterprise Manager old versions, add {<Dim1={"*"}>} inside first SUM and COUNT expressions.

Sum({<Dim1={"*"}>} AGGR ( if ( sum(Step1)> 0.0036334529378426 , sum(Step1) ),Dim1,Dim4) ) / Count({<Dim1={"*"}>}Step1)

And take a look on this links that help you better understand how Qlik Set Analysis works:

Good Luck,

[],

Pedro