Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
georgio_hb
Contributor III
Contributor III

How to Calculate the number of customers who are still existing from last year but moved to a different product line this year?

I tried this out but didn't seem to give any result.

 

 I want the customers that are still existing ( had Sales in  previous year and still have sales this year but on a different product line that the once of last year's.

 

if ( AGGR ( sum({$< Year = {$(v_Max_Year_LY)} >} [Invoice Amount USD]) , [Item Product Line] , [Customer Key] ) > 0
and


AGGR ( sum({$< Year = {$(v_Max_Year)} >} [Invoice Amount USD]) , [Item Product Line] , [Customer Key] ) = 0


and

sum({$< Year = {$(v_Max_Year)} >} [Invoice Amount USD]) > 0

, [Customer Key] )



Labels (1)
8 Replies
GaryGiles
Specialist
Specialist

You want to use the P() Possible and E() Exclude functions.  Something like this:

if(aggr(Sum({$<[Sales Type] = {'Net Sales'},Year={$(v_Max_Year)},[Customer Key]=P({$<Year={$(v_Ma_Year_LY)}>} [Customer Key]), [Item Product Line]=E({$<Year={$(v_Ma_Year_LY)}>} [Item Product Line]) >} [Invoice Amount USD]), [Customer Key])>0 [Customer Key])

georgio_hb
Contributor III
Contributor III
Author

Thank you for you reply , but the given didn't give me any result.

 

Can you please elaborate why and how you used P() and E().

Usama
Creator
Creator

Hi,
Kindly have a look into below link:

https://community.qlik.com/t5/QlikView-App-Development/P-E-and-where-do-you-use-them/td-p/457847

Thanks

From Nothing - To Something - To Everything
georgio_hb
Contributor III
Contributor III
Author

 I Checked it out, but I'm not able to get an number from applying the P() and E():

 

Sum({$< 

[Item Product Line]=P({$<Year={$(v_Max_Year_LY)}>} [Item Product Line]) ,
[Item Product Line]=E({$<Year={$(v_Max_Year)}>} [Item Product Line]) ,
[Customer Key]=P({$<Year={$(v_Max_Year_LY)}>} [Customer Key]),
[Customer Key]=P({$<Year={$(v_Max_Year)}>} [Customer Key]),
Year={$(v_Max_Year)}

>} [Invoice Amount USD])

 

GaryGiles
Specialist
Specialist

Sorry, it's difficult to test an expression when you don't have the data or data model.  I didn't have one of your variables spelled correctly.  Try this as a Dimension:

You want to use the P() Possible and E() Exclude functions.  Something like this:

if(aggr(Sum({$<[Sales Type] = {'Net Sales'},Year={$(v_Max_Year)},[Customer Key]=P({$<Year={$(v_Max_Year_LY)}>} [Customer Key]), [Item Product Line]=E({$<Year={$(v_Max_Year_LY)}>} [Item Product Line]) >} [Invoice Amount USD]), [Customer Key])>0 [Customer Key])

 

georgio_hb
Contributor III
Contributor III
Author

Tried the once you sent, it 's  showing me the Customers that have a new product line.

To Be more specific to what I want:

The customers that had sales last year and still have sales this year, but the Product line that had sales last year this year is zero.

Ksrinivasan
Specialist
Specialist

hi,

find the example it will help you,

Ksrinivasan_1-1612377841099.png

Ksrinivasan_0-1612377829293.png

ksrinivasan

 

georgio_hb
Contributor III
Contributor III
Author

Thanks for the help, but i dont think the aggr & NODISTINCT will help me in finding the lost product lines.