Current year same date and last year same date comparison

[mohanrajsubrama]

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.

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

But the output i needed was like in this format.

Date	Currentyear	Previous year
1/1/2013	1000	0
1/2/2013	2000	0
1/3/2013	450	0
1/1/2014	985	1000
1/2/2014	546	2000
1/3/2014	478	450
1/1/2015	598	985
1/2/2015	476	546
1/3/2015	1456	478

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

[PrashantSangle]

Hi,

If date is your dimension

then for Current Year

Sum(Sales)

for Previous Year

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

Regards

[jonathandienst]

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?

[MK_QSL]

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; 

[jagan]

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.