Product Loss in Table

[marishnagendran]

Dear All,

My Data Set

Year	Product	Qty	Value
2017	Product A	10	200
2017	Product B	20	200
2017	Product C	5	100
2017	Product D	25	500
2018	Product A	40	400
2018	Product B	30	250

My Concept is Product Loss (i.e)

If I select 2018 in a filter then i want to take 2017 sold product list and compare with 2018 sold product if doesn't exist then i want to shown that item

In this example Product C and Product D have to been shown if i select 2018 .

Need help

[agni_gold]

You need to generate products for all relevant cases some things like below 

Product_Year: Load distinct Product from DataSet; join Load distinct Year from DataSet;

Final:

Load Year, Product resident Product_Year; Drop Table Product_Year ;

Left Join

Load Year, Product, Qty, Value from DataSet;

Now it will work. Only thing is your data volume will be increased.

[marishnagendran]

Thanks for your reply agni

In My transaction table I have Date level transaction not in year we maintaining master calendar in separate table and also we are using fiscal year(APR-MAR)

instead of year what i should insert in load statement??

[jonathandienst · Accepted solution]

You can use an expression with a pattern like this:

Concat({<Product = P({<Year = {2017}>} Product) * E({<Year = {2017}>} Product)>} DISTINCT Product, ',')

(All the possible products in 2017 intersecting with all the products not in 2018)

Adapt to your exact requirements.

[jonathandienst]

That should read

Concat({<Product = P({<Year = {2017}>} Product) * E({<Year = {2018}>} Product)>} DISTINCT Product, ',')

[marishnagendran]

If i select customer name in filter the expression not working correctly

[jonathandienst]

Please post a new question as this topic has already been answered. You can add a link back to this topic in the new question.