Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Comparing data over previous years

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.

43 Replies
Not applicable
Author

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

Not applicable
Author

You mean this?

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

Regards,

Henrik

joachim_boivie
Partner - Contributor III
Partner - Contributor III

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)

....

Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

Darek,

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

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

Not applicable
Author

epissarrra

for this "standarisation" choose year, which consists 29.02

Not applicable
Author

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).

Not applicable
Author

should be even easier ....

But i will be able to test it later

Not applicable
Author

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?