22 Replies Latest reply: Oct 16, 2017 7:26 AM by Sunny Talwar

# New Customer and Dead Customer between Max Year and previous Years

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

Thank;s and regards

• ###### Re: New Customer and Dead Customer between Max Year and previous Years

Hi Ghasssan,

Please create flag on script level and use it on front end.

.

.

If(year = '2017'   and Amount >0) as Flag,

From

<Path>

Thanks,

Arvind Patil

• ###### Re: New Customer and Dead Customer between Max Year and previous Years

Sorry Dear but what I am asking for is to create ListBox for New Customer and Dead Customers in

• ###### Re: New Customer and Dead Customer between Max Year and previous Years

Hi Ghassan,

.

.

If(year = '2017'   and Amount >0,Customer) as [New  Customer],

From

<Path>

Thanks,

Arvin Patil

• ###### Re: New Customer and Dead Customer between Max Year and previous Years

Hi ,

Can you please provide any sample data.

• ###### Re: New Customer and Dead Customer between Max Year and previous Years

May be this

New Customer

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

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

• ###### Re: New Customer and Dead Customer between Max Year and previous Years

correct me if i am wrong here sunny but should not be the expression be  like this cause it is to create a list box

New Customer

If(aggr(Sum({<Year = {'\$(=Max(Year))'}>} Sales) ,Customer)> 0 and aggr(Sum({<Year -= {'\$(=Max(Year))'}>} Sales),Customer) = 0, Customer,null())

• ###### Re: New Customer and Dead Customer between Max Year and previous Years

What is the issue with an overarching Aggr()? I mean if you wish to repeat is twice, sure.. but not sure why

• ###### Re: New Customer and Dead Customer between Max Year and previous Years

see attachment

• ###### Re: New Customer and Dead Customer between Max Year and previous Years

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)

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

• ###### Re: New Customer and Dead Customer between Max Year and previous Years

Thank you for help..... But sorry not working

I try to attache example file but I don.t find attachment buttom

for that I mak new post with attachment

http://community.qlik.com/message/1358884#1358884

New Customer and Dead Customer between Max Year and previous Years

plese Help me.......

thanks and regards

• ###### Re: New Customer and Dead Customer between Max Year and previous Years

Use advance editor... You will have attach option at the right bottom..

• ###### Re: New Customer and Dead Customer between Max Year and previous Years

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

• ###### Re: New Customer and Dead Customer between Max Year and previous Years

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 .

• ###### Re: New Customer and Dead Customer between Max Year and previous Years

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)

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

• ###### Re: New Customer and Dead Customer between Max Year and previous Years

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..

• ###### Re: New Customer and Dead Customer between Max Year and previous Years

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))

• ###### Re: New Customer and Dead Customer between Max Year and previous Years

thank you

• ###### Re: New Customer and Dead Customer between Max Year and previous Years

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

• ###### Re: New Customer and Dead Customer between Max Year and previous Years

What is the output you expect to see for the sample data that you have shared with us?

• ###### Re: New Customer and Dead Customer between Max Year and previous Years

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

• ###### Re: New Customer and Dead Customer between Max Year and previous Years

What is the number you are expecting to see for the sample you have provided? Give me a number that I can try to get