Announcements
cancel
Showing results for
Did you mean:
Creator

## how to filter based variables inuput.

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.

 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

Labels (2)