# 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
Did you mean:
Highlighted
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.

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

Labels (3)

• ### scatter plot

1 Solution

Accepted Solutions
Highlighted
Partner

## Re: Scatter plot chart comparison previous year

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

8 Replies
Highlighted
Partner

## Re: Scatter plot chart comparison previous year

Hi,

Try something like this:

``````tmp1Table:
*
Where Year = 2018
;
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)
Category & '_LY' as NewCategory,
*
Where Year = 2017
;
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

Work smarter, not harder
Highlighted
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
Highlighted
Partner

## Re: Scatter plot chart comparison previous year

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

Highlighted
Partner

## Re: Scatter plot chart comparison previous year

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

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

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

Attached!

cheers,

pau

Highlighted
Creator II

## Re: Scatter plot chart comparison previous year

Excellent, I learned something new today!

Thanks,

Olivier