# New to Qlik Sense

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

Announcements
Join us on May 23rd, Q&A with Qlik - Developer Series, Customizing Extensions: Register Today
cancel
Showing results for
Did you mean:
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)
• ### Pivot Measure

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

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

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

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

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.

Specialist

hi,