Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
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

epissarra,

are results of my last version correct?

regards

Darek

Not applicable
Author

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

Not applicable
Author

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>

Not applicable
Author

look:

so.JPG.jpg

Not applicable
Author

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.

Not applicable
Author

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

View solution in original post

Not applicable
Author

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.

Not applicable
Author

You are right

but look:

so.JPG.jpg

Not applicable
Author

I will check

Not applicable
Author

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>