Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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'
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.