Line Chart - Dividing values by last year's value

Ax — Tue, 25 Jan 2022 14:54:00 GMT

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!

Re: Line Chart - Dividing values by last year's value

Qrishna — Wed, 26 Jan 2022 03:54:37 GMT

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.

topic Line Chart - Dividing values by last year's value in Insight Advisor

Line Chart - Dividing values by last year's value

Re: Line Chart - Dividing values by last year's value