Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

date over year comparision

Hi

It is possible to implement the following table:

dateACTACT LY
24-11-201434503600
23-11-201416001800
22-11-201425002400

dimension: date

measures: sales

filter: date >= vTrendStart ; date <= vTrendEnd

This should be easy, right?

Dror

Tags (5)
6 Replies
anbu1984
Honored Contributor III

Re: date over year comparision

Check this app

Not applicable

Re: date over year comparision

Anbu,

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

Dror

anbu1984
Honored Contributor III

Re: date over year comparision

You can use the below expression LY

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

Not applicable

Re: date over year comparision

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

datesales
24-11-20143450
23-11-20141600
22-11-20142500
24-11-20133600
23-11-20131800
22-11-20132400

dimension: date

Employee
Employee

Re: Re: date over year comparision

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

Capture.PNG.png

----------

//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;

Not applicable

Re: date over year comparision

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

Community Browser