Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
mohanrajsubrama
New Contributor II

Current year same date and last year same date comparison

Dear All,

I have a requirement like to compare current year date and last year same date sales.

And i have a data like this below.

DateSales
1/1/20131000
1/2/20132000
1/3/2013450
1/1/2014985
1/2/2014546
1/3/2014478
1/1/2015598
1/2/2015476
1/3/20151456

But the output i needed was like in this format.

DateCurrentyearPrevious year
1/1/201310000
1/2/201320000
1/3/20134500
1/1/20149851000
1/2/20145462000
1/3/2014478450
1/1/2015598985
1/2/2015476546
1/3/20151456478

Current year was the actual sales in that date.And the previous year was same date last year's data .

Example:

If  the date is 1/3/2015,then it should populate 1/3/2014 data in the Previous year column.

Can any one help me on this above requirement.Thanks in Advance

Regards,

Mohanraj Subramanian

4 Replies

Re: Current year same date and last year same date comparison

Hi,

If date is your dimension

then for Current Year

Sum(Sales)

for Previous Year

Sum({Date={"=Date(AddYears(Date,-1))"}}Sales)

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
MVP
MVP

Re: Current year same date and last year same date comparison

I suggest that you upload a sample qvw file with the relevant data loaded (or the source data in a file) for more specific help.

Do you want to do this calculation during load or in the front end?

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MVP
MVP

Re: Current year same date and last year same date comparison

Sales:

Load

  Date(Date#(Date,'D/M/YYYY')) as Date,

  Sales

Inline

[

  Date, Sales

  1/1/2013, 1000

  1/2/2013, 2000

  1/3/2013, 450

  1/1/2014, 985

  1/2/2014, 546

  1/3/2014, 478

  1/1/2015, 598

  1/2/2015, 476

  1/3/2015, 1456

];

Map_Sales:

Mapping Load Date as MapDate, Sales as MapSales Resident Sales;

Final:

Load

  Date,

  Sales,

  ApplyMap('Map_Sales',AddYears(Date,-1),0) as PreYearSales

Resident Sales;

Drop Table Sales; 

MVP
MVP

Re: Current year same date and last year same date comparison

Hi,

Try this script

Data:

LOAD

Date(Date) AS Date,

Sales

INLINE [

Date, Sales

1/1/2013, 1000

1/2/2013, 2000

1/3/2013, 450

1/1/2014, 985

1/2/2014, 546

1/3/2014, 478

1/1/2015, 598

1/2/2015, 476

1/3/2015, 1456];

LEFT JOIN (Data)

LOAD

AddYears(Date, 1) AS Date,

Sales AS PreviousYearSales

RESIDENT Data

WHERE Year(Date) < Year(Today());

Regards,

jagan.

Community Browser