Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
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

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

Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

Please check new version ....

Not applicable
Author

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

Here is version serving also those cases.

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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)