Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
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
Highlighted

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.
Highlighted

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
Highlighted
MVP
MVP

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; 

Highlighted
MVP & Luminary
MVP & Luminary

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.