Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
deep2021
Creator III
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.

 

deep2021_0-1672173595051.png

 

Thanks

Labels (7)
1 Solution

Accepted Solutions
pedrobergo
Employee
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:

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

View solution in original post

9 Replies
sidhiq91
Specialist II
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.

vinieme12
Champion III
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.
pedrobergo
Employee
Employee

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

Hi,

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

Thanks

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

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

pedrobergo
Employee
Employee

Hi @deep2021 

So, your expectation is get this values:

pedrobergo_0-1672254836225.png

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

Hi ,

 

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

 

Thanks

pedrobergo
Employee
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:

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