12 Replies Latest reply: Jul 3, 2013 3:49 AM by sravan Vennu

# Compare Sales for Same DAY Last Year

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

• ###### Compare Sales for Same DAY Last Year

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

• ###### Compare Sales for Same DAY Last Year

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.

• ###### Compare Sales for Same DAY Last Year

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

• ###### Compare Sales for Same DAY Last Year

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

01/01/2010 vs 01/02/2009

etc.....

• ###### Compare Sales for Same DAY Last Year

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

Alex

• ###### Re: Compare Sales for Same DAY Last Year

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.

• ###### Compare Sales for Same DAY Last Year

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)

• ###### Compare Sales for Same DAY Last Year

Looking back at these, they both worked for me. And I got the correct values for the one day. Now is there a way to create a rollup like this? I want to compare each day for the 12 months (365 days) to the previous 12-24 months, 365-730.

Any ideas how I would do that?

• ###### Re: Compare Sales for Same DAY Last Year

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 :

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.

• ###### Re: Compare Sales for Same DAY Last Year

Hi,

I have the same problem but i need it in backend ,means in script.I want to compare sales of this year to previous year but for the same day of previous year.

Rathish

• ###### Re: Compare Sales for Same DAY Last Year

Hi Friends,