date over year comparision

[#1]

Hi

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

[anbu1984]

Check this app

[#3]

Anbu,

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

Dror

[anbu1984]

You can use the below expression LY

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

[#5]

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

[JonnyPoole]

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;

[#7]

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