Skip to main content
Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Showing results for 
Search instead for 
Did you mean: 
Ax
Contributor II
Contributor II

Line Chart - Dividing values by last year's value

Hi!

I would like to create a Line Chart in Qlik Sense that shows the increase in prices compared to the same date last year. In other words, it should show prices divided by last year's prices.

For example

1/1/2020 Price = 1 and 1/1/2021 Price = 1.25 --> Shows 1.25 at 1/1/2021 because 1.25/1 = 1.25.

2/1/2020 Price = 1.5 and 2/1/2021 Price = 3 --> Shows 2 at 2/1/2021 because 3/1.5 = 2.

I want to do this with expressions. Thanks for the help!

1 Reply
krishna_2644
Specialist III
Specialist III

Hi - Please proceed as below

Step1: In your load script, modify your's as per script below. i.e create two fields for month and year

data:
load Date#(date, 'm/d/yyyy') as date,
price,
num(left(date, 1)) as month,
num(right(date, 4)) as year
;
load * inline [
date, price
1/1/2020, 1
1/1/2021, 1.25
2/1/2020, 1.5
2/1/2021, 3
];

Step2: create a line chart, with 3 measures(in fact just one should be fine)

1st measure expression: = only({<year = {"$(=Max(year))"}>} price) - label this expression as 'current year price'
2nd measure expression: = only({<year = {"$(=Max(year)-1)"}>} price) - label this expression as 'previous year price'
3rd measure expression: = [current year price]/[previous year price] - label this expression as 'kpi'

 

Capture.JPG

 

I'm doing this in qlikview as i'm not able to do QS on my local machine, so you gotta put the  same in your QS.