topic date over year comparision in QlikView

date over year comparision

Mon, 24 Nov 2014 10:28:12 GMT

It is possible to implement the following table:

date	ACT	ACT LY
24-11-2014	3450	3600
23-11-2014	1600	1800
22-11-2014	2500	2400

dimension: date

measures: sales

filter: date >= vTrendStart ; date <= vTrendEnd

This should be easy, right?

Dror

Re: date over year comparision

anbu1984 — Mon, 24 Nov 2014 11:52:43 GMT

Check this app

Re: date over year comparision

Mon, 24 Nov 2014 12:15:29 GMT

Anbu,

I'm only missing the LY numbers in your example. In my data model sales of 2013 have their own date

Dror

Re: date over year comparision

anbu1984 — Mon, 24 Nov 2014 12:38:50 GMT

You can use the below expression LY

=Sum({<date = {'>=$(=Date(vMinDate))<=$(=Date(vMaxDate))'} >} [ACT LY])

Re: date over year comparision

Mon, 24 Nov 2014 13:01:18 GMT

I don't have ACT LY as a seperate field. source looks something like that:

date	sales
24-11-2014	3450
23-11-2014	1600
22-11-2014	2500
24-11-2013	3600
23-11-2013	1800
22-11-2013	2400

dimension: date

Re: Re: date over year comparision

JonnyPoole — Mon, 24 Nov 2014 15:06:38 GMT

Here is a data model based solution that may work for you.

----------

//Your Data

RawData:

LOAD * INLINE [

Date, Sales

24-11-2014, 3450

23-11-2014, 1600

22-11-2014, 2500

24-11-2013, 3600

23-11-2013, 1800

22-11-2013, 2400

];

//Sales

NoConcatenate

Data:

Load

Date(Date#(Date,'DD-MM-YYYY')) as Date,

Sales,

addyears(Date(Date#(Date,'DD-MM-YYYY')),-1) as LYDate

Resident RawData;

//Add fields for Last Year Sales

left join(Data)

Load

Date(Date#(Date,'DD-MM-YYYY')) as LYDate,

Sales as LYSales

Resident RawData;

drop table RawData;

Re: date over year comparision

Mon, 24 Nov 2014 17:14:05 GMT

Jonathan,

appreciate your help.

unfotunatly i cannot aggregate ACT LY in the script. It needs to be solved on the chart level.I was thinking of using dayNumberOfYear function in combination with yearStart but i have the leap year issue

something like

DATE(yearStart(TODAY()) + dayNumberOfYear-1)

This works perfect until 28-2 after that it's breaks

Dror