Show Sum of rows in another column in set analysis

[rkpatelqlikview]

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.

[lironbaram · Accepted solution]

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

[rkpatelqlikview]

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.

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

[rkpatelqlikview]

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.

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.