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.

[VishalWaghole]

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

[#3]

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?

[#4]

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.

[#5]

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

[#6]

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.

[#7]

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

[#8]

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.

[#9]

First idea in attachment.

Please consider what to do with 29.02

regards

Darek

[#10]

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

Could copy/paste on Word doc ?

Thank you.