Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

New Customer and Dead Customer between Max Year and previous Years

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

1 Solution

Accepted Solutions
Highlighted

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)

Dead Customers

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

Capture.PNG

View solution in original post

22 Replies
Highlighted
Partner
Partner

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.

Load

.

.

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

From

<Path>



Thanks,

Arvind Patil

Highlighted
Contributor
Contributor

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

Hi ,

Can you please provide any sample data.

Highlighted
Contributor II
Contributor II

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

Highlighted
Partner
Partner

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

Hi Ghassan,

Please do as below:

Load

.

.

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

From

<Path>



Thanks,

Arvin Patil

Highlighted

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)


Dead Customer

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

Highlighted
Master II
Master II

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


Learning never stops.
Highlighted

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

Highlighted
Contributor II
Contributor II

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

Highlighted
Master II
Master II

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

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

Learning never stops.