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

[georgio_hb]

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

[GaryGiles]

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]

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]

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

[georgio_hb]

 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]

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]

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]

hi,

find the example it will help you,

ksrinivasan

[georgio_hb]

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