Compare Sales for Same DAY Last Year

[CB308]

The end user wants to compare sales by DATE for the same date last year.

In the example below, 01/01/2010 Sales are $304,304, the sales listed in Same Day Last Year are for the date (01/02/2009)

Currently I am only showing sales by actual date 01/01/2010 and 01/02/2009 but not next to each other as the end user is looking for...

Any suggestions?

Week #	Day of Week	Date	Sales	Sales Same Day Last Year
1	Friday	1/1/2010	$304,304	$328,120

my results:

Week #	Day of Week	Date	Sales	Sales Same Day Last Year
1	Friday	1/1/2010	$304,034
		1/2/2009	$328,120
	Saturday	1/2/2010	$475,357
		1/3/2009	$338,492

[fernandotoledo]

You need to user set analisys in this case, creating two expressions, one for the actual the other for theprevious year. There are a lot of information about it in the comunity.

I like this example http://community.qlik.com/media/p/82604.aspx

best regards,

Fernando

[johnw]

One way is to generate an AsOf table based on your rules for which day from last year we want to compare to:

AsOfDate, SaleType, Date
1/1/2010, Sales, 1/1/2010
1/1/2010, Sales Same Day Last Year, 1/2/2009
1/2/2010, Sales, 1/2/2010
1/2/2010, Sales Same Day Last Year, 1/3/2009
etc.

Use AsOfDate instead of Date in your pivot table. Add SaleType as a dimension, and move it to the top of the chart. Use sum(Sales) as the expression.

[Miguel_Angel_Baeyens]

Hi,

You mean year to date comparisons? Say this year (01/01 - 28/10) to the same period of the previous year? If so, use the YearToDate() function, that returns true when a date corresponds to a date in a previous year

Sum({< LastYTDFlag = {'-1'}, CurrentYTDFlag = {'-1'} >} Sales)

When you have previously defined those fields in your load script as

YearToDate(DateField, -1) AS LastYTDFlag,YearToDate(DateField) AS CurrentYTDFlag,

Hope that helps

[CB308]

not year to date, i mean actual date versus same date last year.

01/01/2010 vs 01/02/2009

etc.....

[#6]

Did you figure this out candiceb? I have the same exact need.

Alex

[Anonymous]

If you have a calendar linked to your fact table you should be able to accomplish the task with set analysis:

Expression 1

Most recent completed week

Sum( {$<Week=, Date = {'$(=WeekStart(Max(Date),0,0))'}>} [Sales] )

Expression 2

52 weeks prior from the most recent completed week

Sum( {$<Week=, Date = {'$(=WeekStart(Max(Date),-52,0))'}>} [Sales] )

In my examples I am using weeks, you should be able to change WeekStart to DateStart in the expression. In the second example the -52 is setting the week to be selected as 52 weeks in the past from the current week or week that has been selected with a calendar.  Your negative value in expression number two would be the nukmber of days you want to go back in the past and compare to current.

[#8]

Okay so this is what I've modified it to. But I don't get any data with DateStart. But I do when I have WeekStart in the function.

=Sum( {$<Week =,Date= {'$(=DateStart(Max(Date),-1,0))'}>} Cnt)

[Miguel_Angel_Baeyens]

Hi Alex,

Use this

Sum({< DateField = {'$(=Date(Today()))', '$(=Date(AddYears(Today(), -1)))'} >} Amount)

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#10]

Hello,

I face the same need, I solve it his way :

In the following example, I calculate the same work day last year assuming it has the same name ('mon', 'tue.'..) with the function weekday, and the same week number (from 1 to 52) with function week:

My Calendar has fields named Date, WeekDay, Week, Year determined like this :

Load

TempDate as Date,

Week(TempDate) as Week,

Month(TempDate) as Month,

Year(TempDate) as Year,

Day(TempDate) as Day,

WeekDay(TempDate) as WeekDay ...

Sales contains the ... Sales amount

To get :

Today's Sales :

= sum( {<Date={ '$(= today() )' }>} Sales )

Same workday last year Sales :

= sum( { 1< WeekDay = {'$(= weekday( today() ))'} , Week = {'$(=week(today()))'}, Year = {'$(=year(today())-1)'} > } Sales )

It seems to do what you want.