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.

1 Solution

Accepted Solutions
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

43 Replies
VishalWaghole
Specialist II
Specialist II

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

Not applicable
Author

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?

Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

First idea in attachment.

Please consider what to do with 29.02

regards

Darek

Not applicable
Author

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

Could copy/paste on Word doc ?

Thank you.