Product only sold in a particular year ? Ways to Find !!

[shekhar_analyti]

Hi All ,

I want to find list of product Only Sold in Particular year By two ways ;

1) Using Set expression

2) Without using traditional set expression , i.e manipulation at script level

Example :  If product is sold in year 2016 and 2017 then ignore , if that product is sold only in that year then take that

Thanks & Regards

Shekar

[MK_QSL · Accepted solution]

Script level

Data:

LOAD Product,

     Sector,

     Market,

     Symbol,

     Department,

     Year,

     Value

FROM

[Data.xlsx]

(ooxml, embedded labels, table is Sheet1);

Left Join (Data)

Load Product, 1 as Flag Where TotalYears = 1;

Load Product, Count(Distinct Year) as TotalYears Resident Data Group By Product;

Now use

Count({<Flag = 1>}Distinct Product)

Or

Count(Distinct IF(Flag = 1, Product))

Front end only

=COUNT({<Product = {"=COUNT(Distinct Year) = 1"}>}DISTINCT Product)

or

=COUNT({<Product = {"=COUNT({1}Distinct Year) = 1"}>}DISTINCT Product)

[YoussefBelloum]

Hi,

using SET ANALYSIS, here is one example of how to get the list of products sold ONLY in 2016:

sum({$<Product = E({1<Year={'2017'}>})*E({1<Year={'2018'}>})>} Value)

[YoussefBelloum]

Hi mrkachhiaimp‌

on your second solution (front end only), how can you choose that particular year ?

[MK_QSL]

Good solution but this way you can't compare more than 2 years..

[MK_QSL]

Right now I have given solution for only product sold in one year only.

[YoussefBelloum]

this solution is suitable OP's attached data, because he have only 3 years.

you mean I can't add a third E() ?

[MK_QSL]

=COUNT({<Product = {"=COUNT({1}Distinct Year) = 1"}>}DISTINCT Product)

This will work on year selections as well.

Select 2016 and we have 7 products

Select 2017 and we have 0 product

Select 2018 and we have 2 products

[MK_QSL]

You can add but the result looks wrong !

[YoussefBelloum]

and maybe add this to make it static (for 2017 for example)?

=COUNT({<Product = {"=COUNT({1}Distinct Year) = 1 AND Year=2017"}>}DISTINCT Product)