Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

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

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Re: Scatter plot chart comparison previous year

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

Re: Scatter plot chart comparison previous year

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
Highlighted
MVP & Luminary
MVP & Luminary

Re: Scatter plot chart comparison previous year

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

 

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
Highlighted
Partner
Partner

Re: Scatter plot chart comparison previous year

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

Highlighted
Partner
Partner

Re: Scatter plot chart comparison previous year

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

Highlighted
Partner
Partner

Re: Scatter plot chart comparison previous year

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

Re: Scatter plot chart comparison previous year

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

Highlighted
Partner
Partner

Re: Scatter plot chart comparison previous year

Hi @odassier 

Attached!

cheers,

pau

Highlighted
Creator II
Creator II

Re: Scatter plot chart comparison previous year

Excellent, I learned something new today!

Thanks,

Olivier