Qlik Community

Ask a Question

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator III
Creator III

Show Sum of rows in another column in set analysis

Hi All,

Can you please help me on this.Please find the attached file. 

Here i want to show sum of conversion % in another row. 

sum  of conversion % expression would be like this: (Sum (T_Count)/ Sum(C_Count)*100).

I want to show this "SUM of Conversion%" in other row which is 50.80.How can I achieve this?

Thanks in advance.

 

Labels (2)
1 Solution

Accepted Solutions
Partner
Partner

hi 

it's not about set analysis 

it's about aggregations

use this formula sum(aggr(sum(C_Count)/sum(T_Count),T_Count))

but instead of the bold T_Count put your dimension

View solution in original post

3 Replies
Partner
Partner

hi 

it's not about set analysis 

it's about aggregations

use this formula sum(aggr(sum(C_Count)/sum(T_Count),T_Count))

but instead of the bold T_Count put your dimension

View solution in original post

Creator III
Creator III

Thanks for responding Lironbaram,

 

I added the same but giving same value. 9.38.

The below calculation is for conversion %. 

Numerator value is : 32

Denominator value is: 341

Which gives  (32/341)*100 = 9.38. which is correct. But the sum of all the values it will be 75.27. I want to see this 75.27 in another column instead of showing  9.38.

Then finally i can calculate the score. individual value / total value *100. 

Ex: (19.64/75.27) * 100 = Score.

 

conversion.JPG

I tried to get the sum 75.27, but giving 9.38 with below expression. 

((Count(Total distinct {<[Sales] = {">0"},
[Reg Date]={">=$(=Date(Today()-91,'DD/MM/YYYY'))"},
>} [Cust Name]))

/


(( (Count( TOTAL distinct {<
[Tour Date]={">=$(=Date(Today()-91,'M/DD/YYYY'))"}, [Reg Date]=,
MonthYear=>} [Tour])

+
Count(  TOTAL distinct {<
[TDate]={">=$(=Date(Today()-91,'M/DD/YYYY'))"}, [Reg Date]=,[H_Name]={'Raara','Anan'},
MonthYear=>} [Tour])

)
-
(
Count( TOTAL distinct {<
[TDate]={">=$(=Date(Today()-91,'M/DD/YYYY'))"}, [Reg Date]=,
MonthYear=>} [Tour])
+
Count(TOTAL  distinct {<
  [TDate]={">=$(=Date(Today()-91,'M/DD/YYYY'))"}, [Reg Date]=,[HName]={'Raara','Anan'},
  MonthYear=>} [Tour])))))*100

Creator III
Creator III

Thanks for your respond Lironbaram,

Yes. Same approach i implemented in expression. But my expression like below.  Here i want see 75.27 which is sum of all rows. Instead of showing 9.38 which is (32/341) *100, I want to show sum of all the rows in conversion% column.

conversion.JPG

Here is my expression.

 

(

Sum( Aggr((Count(Total distinct {<[Sales] = {">0"},
[Date]={">=$(=Date(Today()-91,'DD/MM/YYYY'))"}>} [C_Count])
)
/
(( (Count(Total distinct {<
[Name]-={'ADas','Test','test'},
[Date]={">=$(=Date(Today()-91,'M/DD/YYYY'))"},
MonthYear=>} [T_Count])

+
Count(Total  distinct {<Particulars={"*"},
[Dup]={'false'},
[Date]={">=$(=Date(Today()-91,'M/DD/YYYY'))"},
MonthYear=>} [T_count]))
*100, Name1, Name2))

)

Thanks in advance.