Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
i have two slider , for min selection using VS1 variable in slider1and vs2 variable slider i am using.. Based Vs1 and vs2 input in slider and i need to filter sum(delqty)>=vs1 and sum(delqty)=< vs2 .
I am calculating vgrowthcm2 an vgrowthdelvqty for each customer, take count of customer whose vgrowthcm2>10 and vgrowthvolume<30 , and the also taking customers names whose are vgrowthcm2>10 and vgrowthvolume<30., like that i have several range of conditions.
Now Based on slider input of vs1 and vs2, need to filter the customer who has sum(delqty)>=vs1 and sum(delqty)=< vs2 .for that customers only i need to take only customers that to calculate growth cm2 and growth volume, and based range of condition it should count in that range.
EX:Kindly how i can add
Customer | vgrowthcm2 | vgrowthvolume | sum(delvqty) |
a | 10 | 12 | 10 |
b | 0 | 0 | 0 |
c | 40 | 30 | 20 |
d | 15 | 10 | 10 |
e | 3 | 12 | 20 |
vs1=0,vs2=15
now to take count of customers whose vgrowthcm2 >=0 and vgrowthcm2<=10 and vgrowthvolume>=0 and vgrowthvolume<= 10 and also sum(delvqty)>=0 and sum(delvqty)=< 15
output of customers whose customers met above conditions
Customer | vgrowthcm2 | vgrowthvolume | sum(delvqty) |
a | 10 | 12 | 10 |
b | 0 | 0 | 0 |
d | 40 | 30 | 20 |
e | 3 | 12 | 20 |
and also sum(delvqty)>=0 and sum(delvqty)=< 15, output of customers whose these conditions.
Customer | vgrowthcm2 | vgrowthvolume | sum(delvqty) |
a | 10 | 12 | 10 |
b | 0 | 0 | 0 |
final count of customer 2,
i have done vgrowthcm2 and vgrowth formula, and count of customers of satified conditions of vgrowthcm2 and vgrowth formula. now to filter based on sum(delvqty) also and final only that count of customer.
Vgrowthcm2:
if(sum(delv_qty)>= $(vs1) and sum(delv_qty)<$(vs2),
if( GetFieldSelections(Period)='YTD',
((sum({1<CYTD ={1} >}cm2m3_m3) -sum({1<PYTD ={1} >}cm2m3_m3) ) /sum({1<PYTD ={1} >}cm2m3_m3))*100,
((sum({1<R12M ={1}>}cm2m3_m3) -sum({1<PR12M ={1} >}cm2m3_m3) ) /sum({1<PR12M ={1} >}cm2m3_m3))*100))
VgrowthVolume:
if(sum(delv_qty)>= $(vs1) and sum(delv_qty)<$(vs2),if( GetFieldSelections(Period)='YTD',((sum({1<CYTD ={1} >}delv_qty) -sum({1<PYTD ={1}>}delv_qty) ) /sum({1<PYTD ={1} >}delv_qty))*100,((sum({1<R12M ={1} >}delv_qty) -sum({1<PR12M ={1} >}delv_qty) ) /sum({1<PR12M ={1} >}delv_qty))*100))
vCountcustomer:
=Sum(Aggr(If($(vGrowthrateCm2) > $(vv1) and $(vGrowthrateVolume)>=($(vv5)) and $(vGrowthrateVolume)<($(vv4)) , 1, 0),sold_to_desc))
Vcustomername:
aggr(if($(vGrowthrateCm2) > $(vv1) and $(vGrowthrateVolume)>=($(vv5)) and $(vGrowthrateVolume)<($(vv4)),sold_to_desc),sold_to_desc)
How to add sum(delv_qty) filter also and get only customer count
this should work: Sum({$<Customer={"=Sum(Qty)>=$(vs1) and Sum(Qty)<=$(vs2)"}>}Qty)