Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

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
Highlighted
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.

Highlighted
Creator
Creator

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??

 

Highlighted

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

Highlighted

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

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

Highlighted

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