topic Re: Year over Year Chart in QlikView

Year over Year Chart

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I would like to create a Year over Year chart (Per month basis). However, the data is a bit complicated. May I know what kind of method I can use for calculating the Year over Year based on below set of data. Thank you.

Date

01-05-2017

10-05-2017

18-06-2017

23-07-2017

03-05-2018

04-05-2018

21-05-2018

01-06-2018

03-06-2018

17-07-2018

18-07-2018

At the end, I would like to see the line chart comparison on the data over 2017 and 2018.

May 2017 - Total 2

June 2017 - Total 1

July 2017 - Total 1

May 2018 - Total 3

June 2018 - Total 2

July 2018 - Total 2

Re: Year over Year Chart

neelamsaroha157 — Tue, 07 Aug 2018 20:55:07 GMT

Would be able to elaborate a little more or provide a sample data and expected output

I am not sure what Total 1/2 are..

Re: Year over Year Chart

johnca — Tue, 07 Aug 2018 21:39:08 GMT

Maybe something like this...?

DATA:

Load

Count(Value) as Count,

Month(Date2) as Month,

Year(Date2) as Year

Group By

Month(Date2),

Year(Date2);

Load *,

Date(Date#(Date,'DD-MM-YYYY'),'M/D/YYYY') as Date2;

Load * Inline [

Date, Value

01-05-2017, 10

10-05-2017, 12

18-06-2017, 15

23-07-2017, 9

03-05-2018, 7

04-05-2018, 11

21-05-2018, 10

01-06-2018, 6

03-06-2018, 5

17-07-2018, 17

18-07-2018, 15

];

Then make a line chart with dimension: Month

And expressions:

Sum ({<Year={$(=Max(Year))}>}Count) with label =Max(Year)

Sum ({<Year={$(=Max(Year)-1)}>}Count) with label =Max(Year)-1

See attached.

HTH,

John

Re: Year over Year Chart

howiekrauth — Tue, 07 Aug 2018 21:51:30 GMT

Look into RANGESUM(). This should be perfect for your issue.

Re: Year over Year Chart

johnca — Wed, 08 Aug 2018 14:09:48 GMT

It might if Rob was looking for the sum of the numeric values. What Rob is asking for is the number of occurrences.

Regards,

John