Comparing data over previous years

[#1]

Hi all,

I would like to compare the turnover for previous years at the same date.

For instance we are the 15th of April 2014, so I would like to compare the turnover at the same date for previous years:

I mean trurnover in 15th April 2009, 15th April 2010, 15 th April 2011 ... 'till 15th 2014 in order to compare the same situation over the years.

An idea how to do that ?

Thanks.

[#2]

script:

let v_year=Year(Today());

LOAD NCDEGPF,

     CREADATE,

     LIVDATE,

     VAL_SO_NET_PRICE,

     if(month(LIVDATE)<=6,'1 sem','2 sem') as SEM,

     year(LIVDATE) as LIV_YEAR,

     year(CREADATE) as CRE_YEAR,

     if(MakeDate($(v_year),month(CREADATE),day(CREADATE))<=Today(),1,0 ) as IS_YTD

FROM

(biff, embedded labels, table is [Feuil1$]);

Dimensions:

CRE_YEAR,

SEM

Expression:

sum({<IS_YTD={1}>}VAL_SO_NET_PRICE)

regards

Darek

[#3]

You mean this?

sum({<Date={'<=$(vPriorYearDate)'}>} VAL_SO_NET_PRICE)

Regards,

Henrik

[joachim_boivie]

You can also use set as follows,

Current date

SUM({< Date={'$(=TODAY())'} >}Value)

Previous Year

SUM({< Date={'$(=ADDYEARS(TODAY(),-1))'} >}Value)

Two years ago

SUM({< Date={'$(=ADDYEARS(TODAY(),-2))'} >}Value)

Three years ago

SUM({< Date={'$(=ADDYEARS(TODAY(),-3))'} >}Value)

....

[#5]

Yes that's it.

But the result is not as expected because you consider only the CREADATE and not the LIVDATE (expected delivery date).

But thanks for your help.

[#6]

epissarra,

i have another one, more dynamic idea.

in script:

let v_year=Year(Today());

LOAD NCDEGPF,

    CREADATE,

    LIVDATE,

    VAL_SO_NET_PRICE,

    MakeDate(1900,month(CREADATE),day(CREADATE)) as STANDARD_DATE,

    if(month(LIVDATE)<=6,'1 sem','2 sem') as SEM,

    year(LIVDATE) as LIV_YEAR,

    year(CREADATE) as CRE_YEAR,

    if(MakeDate($(v_year),month(CREADATE),day(CREADATE))<=Today(),1,0 ) as IS_YTD

FROM

(biff, embedded labels, table is [Feuil1$]);

//all dates putet into year 1900 to have common year

expression:

sum({<STANDARD_DATE={"<=$(=makedate(1900,month($(=v_sel_date)),day($(=v_sel_date))))"}>}VAL_SO_NET_PRICE)

//where v_sel_date is variable with date selected by user.

So, this approach givas you more dynamic solutions

I hope, that it helps

darek

[#7]

Darek,

Thank you very much for your help, you've done it.

This is what i was expecting ... Thank you.

[#8]

epissarrra

for this "standarisation" choose year, which consists 29.02

[#9]

Darek,

In fact it is not really ok.

With this method you consider only the CREADATE.

So you will calculate the turnover for the sales orders created between 20140101 and 20140416 compared with sales orders created between 20130101 and 20130416. And this is not what i want.

I want to have:

For 2014, all the sales orders created before 20140416 AND with expected delivery date within semester1; the same with semester2. So it means i have to consider, for instance, also sales orders created in december 2013 with delivery date in 2014 (check NCDEGPF 2020098058).

With your method, the sales order created the 26th of december 2013 with delivery date the 8th of July 2014 is excluded and it should not. It should be part of the turnover of 2014 semester2 (check NCDEGPF 2020098058).

[#10]

should be even easier ....

But i will be able to test it later

[#11]

If i understand good, you should consider only delivery date. It is not important when document was created. It is important only when it is expected to be delivered .... Isnt it?