topic Re: Re: Comparing data over previous years in QlikView

Comparing data over previous years

Tue, 15 Apr 2014 13:14:01 GMT

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.

Re: Comparing data over previous years

VishalWaghole — Tue, 15 Apr 2014 14:00:11 GMT

Hi,

Use this,

addmonths (YourDateColumn,12) for previous year

addmonths (YourDateColumn,24) for previous to previous year

and so on.....

Hope it will help you.

- Regards,

Vishal Waghole

Re: Comparing data over previous years

Tue, 15 Apr 2014 14:52:09 GMT

Depending what is your data volume you and what other analytics you would like to have in your application, you may want to prepare your data model to support those comparisions. Of course preparing your expressions in dynamic manner will be useful. It all depends what you want to show your user and what selections user can make. How many years and how much rows per year do you assume?

Re: Comparing data over previous years

Wed, 16 Apr 2014 07:44:40 GMT

Hi,

Thanks for your anwsers.

In my database I have the turnover starting at year 2009 and i would like to compare every year at the same current date.

User won't choose any date. The date to consider is the current date (when the report is open).

Now let's explain better.

I have sales orders with creation date and expected delivery date.

I manage two periods during the year: the first 6 month (january - June) and the last 6 month (july - december).

A sales order can be created today and to be delivered in 3 or 6 month.

The date to be considered for the turnover calculation is the expected delivery date, because the expected delivery date is also the expected invoice date.

The expected result: today we are the 16th of April 2014.

I would lke to compare the value of all the sales orders created before 16th April 2014 AND with delivery date:

1/ between 2014/01/01 and 2014/06/01 - First part of the year (semester 1)

2/ between 2014/07/01 and 2014/12/31 - Second part of the year (semester 2)

And the same for the previous years. All the sales orders created before the 16th April 2013 AND with delivery date:

1/ between 2013/01/01 and 2013/06/01

2/ between 2013/07/01 and 2013/12/31

... and the same for 2012, 2011, 2010 and 2009 to compare the performance of each year at the same period.

Thanks for your feedback.

Re: Comparing data over previous years

Wed, 16 Apr 2014 08:20:00 GMT

Hi epissarra,

have you already tryed to load your data into QV? If yes, please share sample with some test data. It will be easier to propose something. If not - anyway prepare and share some xls table with sample of your data.

regards

Darek

Re: Re: Comparing data over previous years

Wed, 16 Apr 2014 08:39:26 GMT

Dariusz,

Here is the table for 2013/2014 of sales orders. The field LIVDATE is the expected delivery date.

I already loaded values in QV and already have some reports.

The field VAL_SO_NET_PRICE is the value of the sales order (there is a dummy value).

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

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)

... for 2012 'till 2009.

Re: Comparing data over previous years

Wed, 16 Apr 2014 09:12:17 GMT

Hi,

You could define variables in the load script, don't know if this is a smart way to do it but I use it.

Ex:

//For last year

let vPriorYearDate = '=date(addyears(Now(),-1),' & chr(39) & 'DD.MM.YYYY' & chr(39) & ')';

//2 years back

let vPriorYearDate2 = '=date(addyears(Now(),-2),' & chr(39) & 'DD.MM.YYYY' & chr(39) & ')';

And change the date format (I use DD.MM.YYYY) to the one you use.

Then in the expression use set analysis ex:

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

Regards,

Henrik

Re: Re: Comparing data over previous years

Wed, 16 Apr 2014 09:51:28 GMT

Hi,

The SUM should be applied on DATE <= vPriorYearDate and not Date = vPriorYearDate.

How do you build the SUM with less or equal condition ?

Thanks.

Re: Re: Re: Comparing data over previous years

Wed, 16 Apr 2014 09:57:32 GMT

First idea in attachment.

Please consider what to do with 29.02

regards

Darek

Re: Re: Comparing data over previous years

Wed, 16 Apr 2014 10:05:42 GMT

Thanks but with my personnal edition i cannot open your attached document.

Could copy/paste on Word doc ?

Thank you.

Re: Re: Comparing data over previous years

Wed, 16 Apr 2014 10:24:45 GMT

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

Re: Re: Comparing data over previous years

Wed, 16 Apr 2014 10:31:19 GMT

You mean this?

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

Regards,

Henrik

Re: Comparing data over previous years

joachim_boivie — Wed, 16 Apr 2014 10:33:08 GMT

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)

....

Re: Re: Comparing data over previous years

Wed, 16 Apr 2014 12:09:23 GMT

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.

Re: Comparing data over previous years

Wed, 16 Apr 2014 12:34:49 GMT

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

Re: Re: Comparing data over previous years

Wed, 16 Apr 2014 14:05:08 GMT

Darek,

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

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

Re: Re: Comparing data over previous years

Wed, 16 Apr 2014 14:16:49 GMT

epissarrra

for this "standarisation" choose year, which consists 29.02

Re: Re: Comparing data over previous years

Wed, 16 Apr 2014 15:31:04 GMT

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

Re: Re: Comparing data over previous years

Wed, 16 Apr 2014 15:37:36 GMT

should be even easier ....

But i will be able to test it later

Re: Re: Comparing data over previous years

Wed, 16 Apr 2014 15:43:37 GMT

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?