topic Re: Current year same date and last year same date comparison in QlikView

Current year same date and last year same date comparison

mohanrajsubrama — Mon, 13 Apr 2015 05:57:56 GMT

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

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

PrashantSangle — Mon, 13 Apr 2015 06:03:34 GMT

Hi,

If date is your dimension

then for Current Year

Sum(Sales)

for Previous Year

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

Regards

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

jonathandienst — Mon, 13 Apr 2015 06:06:49 GMT

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?

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

MK_QSL — Mon, 13 Apr 2015 06:57:33 GMT

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;

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

jagan — Mon, 13 Apr 2015 07:10:03 GMT

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.