Set Expression

[neha_shirsath]

Hello Community,

I need your help for below problem.

I'm using this table-

Prdct grp	Prdct Sub grp	Column(1)	Column(2)
		1356.88	7
Buscuits	Choco	31.81	0
Buscuits	Crm	37.39	0
Buscuits	milk	91.39	1
Chocolates	Choco	119.36	1
Chocolates	Crm	52.62	0
Chocolates	milk	104.29	1
Dry Fruits	peanuts	47.11	0
Dry Fruits	pista	106.24	1
Eggs	Eggs	80.71	1
Fruits	apple	99.36	1
Fruits	banana	73.50	0
Fruits	cherry	95.36	1
Fruits	Mango	78.80	0
Fruits	orange	241.44	0
Pizza	Capsicum-Cheese	42.79	0
Pizza	Cheese	54.70	0

I want to calculate this condition-

Column(3)=count(1ce coming in column(2))/total count(1ce + zeros from column(2))

That is- Column(3)=7/16= 0.43 (Single O/p)

Prdct grp	Prdct Sub grp	Column(1)	Column(2)	Column(3)
		1356.88	7	0 .43
Buscuits	Choco	31.81	0	0 .43
Buscuits	Crm	37.39	0	0 .43
Buscuits	milk	91.39	1	0 .43
Chocolates	Choco	119.36	1	0 .43
Chocolates	Crm	52.62	0	0 .43
Chocolates	milk	104.29	1	0 .43
Dry Fruits	peanuts	47.11	0	0 .43
Dry Fruits	pista	106.24	1	0 .43
Eggs	Eggs	80.71	1	0 .43
Fruits	apple	99.36	1	0 .43
Fruits	banana	73.50	0	0 .43
Fruits	cherry	95.36	1	0 .43
Fruits	Mango	78.80	0	0 .43
Fruits	orange	241.44	0	0 .43
Pizza	Capsicum-Cheese	42.79	0	0 .43
Pizza	Cheese	54.70	0	0

Thanks & Regards,

Neha

[nirav_bhimani]

HI,

Kindly find the attached solution.

This is what you want as a output?

Regards,

Nirav Bhimani

[Gysbert_Wassenaar · Accepted solution]

Try: sum(total if(aggr(100*sum(A)/sum(B),[Prdct Sub grp],[Prdct grp])>=80 and aggr(100*sum(A)/sum(B),[Prdct Sub grp],[Prdct grp]) <=120,1))/sum(total aggr(1, [Prdct grp],[Prdct Sub grp]))

[kaushiknsolanki]

Hi,

     Have a look at the application attached.

Regards,

Kaushik Solanki