New Customer and Dead Customer between Max Year and previous Years

[ghassanaldalati]

Hi All... Please help me.

I am create ListBox for New Customer have sales in max selected Years and have no Sales in previous Years from Years selected Rang

if I select Year 2014 - 2015 - 2016 - 2017 and I want to show only the new 2017 customer with sales amount

" =if(sum({$<Year={"$(=max(Year))"}>}Sales)=0 and sum(Sales)>0  then show me the new customers with sales amount "

also I need to create another customer list for dead costumes have sales in previous years and have no sales in max year

please help me how to use IF statement in my expression .

Thank;s and regards

[ghassanaldalati]

see attachment please

[ghassanaldalati]

see attachment

[pradosh_thakur]

Hi

i guess when you select 2014-1017 the result will be only customer 10 as others have sales in the previous years. Cust8 and cust9 has sales in 2016.

i guess Everything else is fine. Please check your calculation. I am attaching the QVW. Please filter the data by customer in straight table and check it again.

regards

Pradosh

[sunny_talwar]

May be try this expression for New customer

=Aggr(If(Sum({<Year -= {'$(=Min(Year))'}>} Sales) > 0 and Sum({<Year = {'$(=Min(Year))'}>} Sales) = 0, CustName), CustName)

Dead Customers

=Aggr(If(Sum({<Year = {'$(=Max(Year))'}>} Sales) = 0 and Sum({<Year -= {'$(=Max(Year))'}>} Sales) > 0, CustName), CustName)

[ghassanaldalati]

Thank you... working Great with case ( the customer have {=0} sales in previous years and have {>0} sales in current year.. 

but .... I have another case when the customer have no name in previous year ( not available in previous year data) and have {>0} sales in current year.. how can i show him in New customer list .

see attachment when i delete customer 9 from 2014 data ... the customer disappered fro New customer list

how can i show both 0 value customer and not missing customer.....

plese help me .

thank you for your efforts

[sunny_talwar · Accepted solution]

Did you try my expression? Seems to be working

New Customers

=Aggr(If(Sum({<Year -= {'$(=Min(Year))'}>} Sales) > 0 and Sum({<Year = {'$(=Min(Year))'}>} Sales) = 0, CustName), CustName)

Dead Customers

=Aggr(If(Sum({<Year = {'$(=Max(Year))'}>} Sales) = 0 and Sum({<Year -= {'$(=Max(Year))'}>} Sales) > 0, CustName), CustName)

[ghassanaldalati]

Dear Sunny.

can you help me ho to get summary of new customer on the title of ( New Customer List )

I try :

sum(Aggr(If(Sum({<Year -= {'$(=Min(Year))'}>} Sales) > 0 and Sum({<Year = {'$(=Min(Year))'}>} Sales) = 0, CustName), CustName))     not working..

[sunny_talwar]

Looking to count the number of customers? Try this

=Count(Aggr(If(Sum({<Year -= {'$(=Min(Year))'}>} Sales) > 0 and Sum({<Year = {'$(=Min(Year))'}>} Sales) = 0, CustName), CustName))

For Sales you can try this

=Sum(Aggr(If(Sum({<Year -= {'$(=Min(Year))'}>} Sales) > 0 and Sum({<Year = {'$(=Min(Year))'}>} Sales) = 0, Sum(Sales)), CustName))

[ghassanaldalati]

thank you

[ghassanaldalati]

please ....Last question ....

in case of "Dead Customer".. between min year   and max year.

you help me a lot to filter names for Dead customers.( how have no sales or negative sales on max year and have positive sales in min year

i am using this expression to count number of Dead customer:

Count(Aggr(If(Sum({<Year = {'$(=Max(Year))'}>} GrossSales_Delv) <= 0 and Sum({<Year -= {'$(=Max(Year))'}>} GrossSales_Delv) > 0, CustName), CustName))& '-Dead Customer '

and i am use this expression to filter Dead customer on listBox

=Aggr(If(Sum({<Year = {'$(=Max(Year))'}>} GrossSales_Delv) <= 0 and Sum({<Year -= {'$(=Max(Year))'}>} GrossSales_Delv) > 0, CustName), CustName)

but how can i get in listbox title , the summary total in min year for Dead Customers in max year 

in another word .. ( the diffrent between Max Year and Min Year for The dead customer how have no sales or negative sales on max year and have positive sales in min year

sorry again for my complecated qusition...

thank you fro your cooperation and regards