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]

Of course regarding that you consider only documents created before selected date ....

[#3]

You have to consider both, CREADATE and LIVDATE.

The starting CREADATE is the current date. So for year 2014 you will consider all the documents created before 20140416; the selection will be done by the DELIVERY date that should be included in semester1 of 2014 then semester2 of 2014.

The same with 2013. You will consider all the documents created before 20130416; the selection will be done by the DELIVERY date that should be included in semester1 of 2013 then semester2 of 2013.

[#4]

But what with docs created before 20130416 but with delivery in 1 semester of 2014 ?

[#5]

When you are working on year 2014, you consider all the documents created before 20140416 with a delivery date in 2014; so if a document has been created in 20130120 but the delivery date is in 2014 you have to consider it.

When you are working on year 2013, you consider all the documents created before 20130416 with a delivery date in 2013. So the document created in 20130120 won't be considered (even if it is before 20130416) because the delivery date is not in 2013 but in 2014.

The logic is:

For 2014:

- semester 1, sum VAL_SO_NET_PRICE for CREADTE <= 20140416 AND LIVDATE between (20140101 and 20140630)

- semester 2, sum VAL_SO_NET_PRICE for CREADTE <= 20140416 AND LIVDATE between (20140701 and 20141231)

so it means I have to consider also the sales orders created in 2013 or 2012 or 2011 ... with delivery date in 2014.

For 2013:

- semester 1, sum VAL_SO_NET_PRICE for CREADTE <= 20130416 AND LIVDATE between (20130101 and 20130630)

- semester 2, sum VAL_SO_NET_PRICE for CREADTE <= 20130416 AND LIVDATE between (20130701 and 20131231)

so it means I have to consider also the sales orders created in 2012 or 2011 or 2010 ... with delivery date in 2013.

[#6]

so documents with CREADTE > 20130416 AND LIVDATE in 2013 will not be considered, yes?

[#7]

Please check new version ....

[#8]

Uff, i found, that sometimes LIVADATE is less than CREDATE!!!!

Here is version serving also those cases.

[#9]

"So documents with CREADTE > 20130416 AND LIVDATE in 2013 will not be considered, yes?"

Yes, if you consider the year 2013.

Could you Copy/paste the script. I cannot open it with my Personnal Edition.

Thanks.

[#10]

hi,

script:

let v_year=Year(Today());

LOAD

rowno() as id,

NCDEGPF,

     CREADATE,

     LIVDATE,

     VAL_SO_NET_PRICE,

     MakeDate(1904,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 (year(LIVDATE)<=     year(CREADATE),1,0) as SAME_YEAR_FL,

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

FROM

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

expression:

sum({$-<STANDARD_DATE={">$(=makedate(1904,month($(=v_sel_date)),day($(=v_sel_date))))"},SAME_YEAR_FL={1}>}VAL_SO_NET_PRICE)

dimensions:

CRE_YEAR,

SEM

Check it and let me know if it is ok

[#11]

I think, it should be considered one more case

script:

let v_year=Year(Today());

LOAD

rowno() as id,

NCDEGPF,

     CREADATE,

     LIVDATE,

     VAL_SO_NET_PRICE,

     MakeDate(1904,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 (year(LIVDATE)=     year(CREADATE),1,0) as SAME_YEAR_FL,

          if (year(LIVDATE)<     year(CREADATE),1,0) as LESS_YEAR_FL,

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

FROM

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

expression:

sum({$-(<LESS_YEAR_FL={1}>+<STANDARD_DATE={">$(=makedate(1904,month($(=v_sel_date)),day($(=v_sel_date))))"},SAME_YEAR_FL={1}>)}VAL_SO_NET_PRICE)