Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi @deep2021 ,
You can:
1. Uncheck Include null values on Dim1 column
or
2. Adding Set modifier to the formula
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://www.youtube.com/watch?v=sB71KUcnwls
Good Luck,
[],
Pedro
@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.
try below
= AVG( AGGR ( if ( sum(Step1)> 0.0036334529378426 , sum(Step1) ),Dim3,Dim4) )
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
Hi,
I need w.r.t Dim1 and Dim4 .The above formula is not working.
Thanks
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
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
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
Hi ,
In above table summation of Dim1 = NOBEL MEADOW. We need to ignore values for nulls.
Thanks
Hi @deep2021 ,
You can:
1. Uncheck Include null values on Dim1 column
or
2. Adding Set modifier to the formula
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://www.youtube.com/watch?v=sB71KUcnwls
Good Luck,
[],
Pedro