Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

candiceb
New Contributor

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 WeekDateSalesSales Same Day Last Year
1Friday1/1/2010$304,304$328,120


my results:

Week #Day of WeekDateSalesSales Same Day Last Year
1Friday1/1/2010$304,034
1/2/2009$328,120
Saturday1/2/2010$475,357
1/3/2009$338,492


12 Replies
fernandotoledo
Valued Contributor

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

MVP
MVP

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.

MVP
MVP

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

candiceb
New Contributor

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

Not applicable

Compare Sales for Same DAY Last Year

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


Alex

epdemeo1
New Contributor

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.

Not applicable

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)

MVP
MVP

Re: Compare Sales for Same DAY Last Year

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

Not applicable

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 :

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.

Community Browser