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: 
pcostapa
Partner - Contributor III
Partner - Contributor III

Scatter plot chart comparison previous year

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 chartdesired chart

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

Thank you

Pau

Labels (3)
1 Solution

Accepted Solutions
pcostapa
Partner - Contributor III
Partner - Contributor III
Author

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:

Captura.PNG

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

pau

View solution in original post

8 Replies
JordyWegman
Partner - Master
Partner - Master

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:

2019-07-24 11_05_25-.png

Jordy

Climber

Work smarter, not harder
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

 

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
pcostapa
Partner - Contributor III
Partner - Contributor III
Author

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:

Captura.PNG

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

pau

pcostapa
Partner - Contributor III
Partner - Contributor III
Author

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
Partner - Master
Partner - Master

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

Work smarter, not harder
odassier
Creator II
Creator II

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
Partner - Contributor III
Partner - Contributor III
Author

Hi @odassier 

Attached!

cheers,

pau

odassier
Creator II
Creator II

Excellent, I learned something new today!

Thanks,

Olivier