Help on set expression with Different Scenarios .

[shekhar_analyti]

Hi All ,

Need help on set expression with following scenarios .

1) How to Find Number of Customers who Did not Purchase any Item in Year 2015 ?

2) How to Find Number of Customers who Did not Purchase any Item in Year 2015 But have Done Purchase in Year 2016 ?

3) How to Find Customer who Purchased More Number of Items ?

4) How to Find Customer who Purchased More TYPES of Items ?

5) How to Find Number of Customers who have Purchased more than one type of Item in Year 2015 ?

6) How to show item least purchased ?

Also Find sample Attached .

Thanks & Regards

Shekar

[vinieme12]

Can't open the App ,using PE 

What is the expression you are using?

In your sample data all customers have purchases in both 2015 and 2016, so i would expect 0 results

[vinieme12]

I've manually removed 2015 sales for two people in the dataset

Load Script:

LOAD Customer,

     Date as DateIS,

     Year(Date) as YearIS,

     Item

FROM

(ooxml, embedded labels, table is Sheet1);

Expression

=count({< Customer ={"=min(TOTAL <Customer> YearIS)=2016"} >} distinct Customer)

Result

[shekhar_analyti]

Updated excel

=count({<Customer= p({"=Count({<Year={2016}>} Item)<>0"}) - p({"=Count({<Year={2015}>} Item)=0"})>}Distinct Customer)

[shekhar_analyti]

set expression with p and e in needed ... i have goofed up somewhere ..

[vinieme12]

even in this excel there are no customers that satisfy the condition of having the first purchase in 2016,

Can you pick one customer as example?

[shekhar_analyti]

Ann Baker  did not purchase anything in 2015 , but in 2016 she purchase refill ..

[vinieme12]

See attached, removed few rows for other people as example

[shekhar_analyti]

Thanks for reply ...

It seems i led to confusion here . Apologies . 

Actually i was looking for expression which would get me records highlighted in yellow .

For Janet in 2015 Item is null but in 2016 it has lamp .. I was looking for such records .

Thanks & Regards

Shekar

[vinieme12]

Try below Customers with null items in 2015 but with possible items in 2016

=count({< Customer = {"=count({<YearIS={2015}>} Item)=0"}> * <Customer = {"=count({<YearIS={2016}>} Item)>0"}>}distinct Customer)

[sunny_talwar · Accepted solution]

In a single set analysis... may be like this

=Count(DISTINCT {< Customer = {"=Count({<YearIS = {2015}>} Item) = 0 and Count({<YearIS = {2016}>} Item)>0"}>} Customer)