topic Compare Sales for Same DAY Last Year in QlikView

Compare Sales for Same DAY Last Year

CB308 — Fri, 29 Oct 2010 17:30:49 GMT

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

fernandotoledo — Fri, 29 Oct 2010 17:43:30 GMT

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

johnw — Fri, 29 Oct 2010 17:44:39 GMT

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

Miguel_Angel_Baeyens — Fri, 29 Oct 2010 17:47:56 GMT

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

CB308 — Fri, 29 Oct 2010 18:14:47 GMT

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

Wed, 19 Oct 2011 15:59:09 GMT

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

Alex

Re: Compare Sales for Same DAY Last Year

Anonymous — Wed, 19 Oct 2011 16:55:49 GMT

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

Wed, 19 Oct 2011 17:42:31 GMT

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)

Re: Compare Sales for Same DAY Last Year

Miguel_Angel_Baeyens — Wed, 19 Oct 2011 18:14:07 GMT

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

Re: Compare Sales for Same DAY Last Year

Sun, 20 Nov 2011 17:54:18 GMT

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.

Re: Compare Sales for Same DAY Last Year

Mon, 12 Dec 2011 07:30:31 GMT

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.

Thanks in advance,

Rathish

Compare Sales for Same DAY Last Year

Fri, 04 May 2012 18:25:26 GMT

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

sravan_v007 — Wed, 03 Jul 2013 07:49:37 GMT

Hi Friends,

I think this will help you...

It contains diffrent YTD calculations using Set Analysis....