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]

epissarra,

are results of my last version correct?

regards

Darek

[#3]

Darek,

The result is not as expected. In fact the selected date in v_sel_date does not have any impact on 2014 value. If i insert the date 20140101, the turnover for 2014 is the same as if i insert 20140417.

But it helps me to find the way. I'm working on it to find a solution ...

[#4]

Hi,

May you Send table with expexted results for few cases? I may then look on

it

17-04-2014 16:11 użytkownik "epissarra" <qcwebmaster@qlik.com> napisał:

Qlik Community <http://community.qlik.com/> Re: Comparing data

over previous years

reply from epissarra<http://community.qlik.com/people/epissarra?et=watches.email.thread>in *Creating

Analytics* - View the full discussion<http://community.qlik.com/message/511303?et=watches.email.thread#511303>

[#5]

look:

[#6]

Darek,

On your screenshot, CRE_YEAR is the dimension. But I use LIV_YEAR as dimension because LIV_YEAR defines the season (sem1=PERM or sem2=FA) and not CRE_YEAR. This is what i use (with v_sel_date = 20140417):

And here is the result with v_sel_date = 20140201

As you can see it is not correct but should be linked to the dimension LIV_YEAR vs CRE_YEAR.

[#7 · Accepted solution]

aha!!!

My season was substracted from LIVEDATE

If you want to have season of CREDATE, we must change it in script

try with this 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$]);

it should work now with chart dimension: LIV_YEAR

Anyway, i'am almost sure that it may be some "analitical"" issue rather than technical problem

let me know if it is better now

regards

Darek

let me know if it is better now

[#8]

The script you propose is the same as the previous one. There is no difference.

The season is correctly managed with "if(month(LIVDATE)<=6,'PERM','FA') as SEASON," the problem is with the dimension. You use CRE_YEAR and i use LIV_YEAR as dimension for turnover calculation. And with LIV_YEAR as dimension, the result is not as expected.

Indeed i think there is no technical problem, but i don"t know how to technically realize my analitical request.

[#9]

You are right

but look:

[#10]

I will check

[#11]

Test it. And check If from business point of view livedate may be less then

credate. I found that kind of rows in your sample data.

18-04-2014 10:35 użytkownik "epissarra" <qcwebmaster@qlik.com> napisał:

Qlik Community <http://community.qlik.com/> Re: Comparing data

over previous years

reply from epissarra<http://community.qlik.com/people/epissarra?et=watches.email.thread>in *Creating

Analytics* - View the full discussion<http://community.qlik.com/message/511653?et=watches.email.thread#511653>