Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
marishnagendran
Creator
Creator

Product Loss in Table

Dear All,

My Data Set

YearProductQtyValue
    
2017Product A10200
2017Product B20200
2017Product C5100
2017Product D25500
2018Product A40400
2018Product B30250

 

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

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

6 Replies
agni_gold
Specialist III
Specialist III

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
Creator
Creator
Author

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
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonathandienst
Partner - Champion III
Partner - Champion III

That should read

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
marishnagendran
Creator
Creator
Author

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

jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein