summation based on condition of other expressions

[deep2021]

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

[sidhiq91]

@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.

[vinieme12]

try below

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

[pedrobergo]

Hi @deep2021 

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

[deep2021]

Hi,

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

Thanks

[deep2021]

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

[deep2021]

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

[pedrobergo]

Hi @deep2021 

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

[deep2021]

Hi ,

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

Thanks

[pedrobergo · Accepted solution]

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:

https://help.qlik.com/en-US/sense/November2022/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/SetAn... 

https://www.youtube.com/watch?v=sB71KUcnwls 

Good Luck,

[],

Pedro