Scatter plot chart comparison previous year

[pcostapa]

Hi guys,

I need some help in a scatter plot chart. I want to select top 5 most selling categories in 2018 and see the same categories in 2017. Top most selling categories apply only to 2018 and not 2017. For example in the below data I'm expecting to visualize: cars, Bicycle, Bike, Smartphones and Laptops although laptops are not the most selling categories in 2017.

load * Inline [
Year, Customer, Category, Sales, Forecast
2018, A, Cars, 100, 120
2017, A, Cars, 80, 100
2018, A, Bicycle, 40, 30
2017, A, Bicycle, 30, 30
2018, B, Pencils, 10, 8
2017, B, Pencils, 5, 7
2018, C, Bike, 150, 140
2017, C, Bike, 155, 160
2018, C, Smartphones, 200, 260
2017, C, Smartphones, 250, 240
2018, C, Laptops, 300, 50
2017, C, Laptops, 3, 5
];

I'm using category as dimension, and this two metric: Sum({<Year = {$(=$(vYear))}>}[Sales]) and Sum({<Year = {$(=$(vYear))}>}[Forecast]).

Extra bonus is to get a relationship between colors as shown below:

desired chart

Here you can see perfectly current year vs previous year, where lighter color means previous year.

Thank you

Pau

[JordyWegman]

Hi,

Try something like this:

tmp1Table: 
Load
	*
Where Year = 2018
;
load * Inline [
Year, Customer, Category, Sales, Forecast
2018, A, Cars, 100, 120
2017, A, Cars, 80, 100
2018, A, Bicycle, 40, 30
2017, A, Bicycle, 30, 30
2018, B, Pencils, 10, 8
2017, B, Pencils, 5, 7
2018, C, Bike, 150, 140
2017, C, Bike, 155, 160
2018, C, Smartphones, 200, 260
2017, C, Smartphones, 250, 240
2018, C, Laptops, 300, 50
2017, C, Laptops, 3, 5
];

Rename field Category to NewCategory;

Concatenate(tmp1Table)
Load
Category & '_LY' as NewCategory,
*
Where Year = 2017
;
load * Inline [
Year, Customer, Category, Sales, Forecast
2018, A, Cars, 100, 120
2017, A, Cars, 80, 100
2018, A, Bicycle, 40, 30
2017, A, Bicycle, 30, 30
2018, B, Pencils, 10, 8
2017, B, Pencils, 5, 7
2018, C, Bike, 150, 140
2017, C, Bike, 155, 160
2018, C, Smartphones, 200, 260
2017, C, Smartphones, 250, 240
2018, C, Laptops, 300, 50
2017, C, Laptops, 3, 5
];

Result:

Jordy

Climber

[kaushiknsolanki]

Hi,

Use this expressions.

Sum({<Year = {"$(=max(Year))"},Category ={'=Rank(Sum({<Year = {"$(=max(Year))"}>}Sales))<=5'} >}[Sales])

Sum({<Year = {"$(=max(Year))"},Category ={'=Rank(Sum({<Year = {"$(=max(Year))"}>}Sales))<=5'} >}Forecast)

Regards,

Kaushik Solanki

[pcostapa · Accepted solution]

Hi @JordyWegman 

Thank you for your approach. I got the idea and made some changes. I keep the original script and created a calculated dimension: =If (Year=$(vYear), Category, Category&'_PY'). For metrics I used Sum({<Category ={'=Rank(Sum({<Year = {$(=$(vYear))}>}Sales))<=5'} >}[Sales]) and same for forecast as @kaushiknsolanki  suggested.

Besides I keep the original category value and even for the color works better:

By the way, is it possible to force qlik sense to write all bubbles labels?

pau

[pcostapa]

hi @kaushiknsolanki 

Thank you for your replay. I think this approach will only show 5 bubbles wheras I need to show 5 bubbles. Actually I think you can accomplish the same result limiting the dimension in first 5 based on the expression of sales.

pau

[JordyWegman]

Hi,

Good! Yes that is possible, go to your object properties -> Presentation ->  Labels -> All.

Please mark the post as solved if your problem is solved so other people can see the solution!

Jordy

Climber

[odassier]

Hi Pcostapa,

Can you share the .qvf for this result please?  Looks very interesting and applicable to some of my scatter charts as well.

Thanks,

Olivier

[pcostapa]

Hi @odassier 

Attached!

cheers,

pau

[odassier]

Excellent, I learned something new today!

Thanks,

Olivier