topic Show Sum of rows in another column in set analysis in App Development

Show Sum of rows in another column in set analysis

rkpatelqlikview — Sat, 16 Nov 2024 02:50:54 GMT

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.

Re: Show Sum of rows in another column in set analysis

lironbaram — Sun, 29 Mar 2020 19:04:21 GMT

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

Re: Show Sum of rows in another column in set analysis

rkpatelqlikview — Mon, 30 Mar 2020 07:56:18 GMT

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

Re: Show Sum of rows in another column in set analysis

rkpatelqlikview — Tue, 31 Mar 2020 06:58:13 GMT

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.