Comparing Data from 2 different dates on a Pivot table

[camilo]

Hi,

I´m trying to compare sales data between 2 different dates (or periods), all the info comes from the same table.

Right now I´m using this to compare the data "=sum({$ <Year={2010}>} Sales)" but it has a glitch, it compares exatly the same day in both years but I need to compare equivalent days meening I need to compare the sales of the first monday of august 2011 with the first monday of august 2010.

My qvw is to heavy to upload it as an example ¿is there any way someone can help me?

Thanks!

Camilo

[swuehl]

Hi Camilo,

if you want to compare the same weekday exactely one year ago, I think you can use this to get the date:

=if((num(vCurrentDate)-num(addyears(vCurrentDate,-1)))=365,

makedate(year(vCurrentDate)-1,month(vCurrentDate),day(vCurrentDate)+1),

makedate(year(vCurrentDate)-1,month(vCurrentDate),day(vCurrentDate)+2))

As of today 2011-08-11, a Thursday, it will return 2010-08-12, also a thursday.

If you want to take the weekdays appearance within the month into account, like really compare the first, second, third Monday of August 2011 with first, second, third Monday of August 2010, that makes it a lot more complicated since e.g. if Jan 1st is a Monday, that's the first Monday of January, right? The following year, the first Monday will be January 7th.

I can think of a way to create a appearance of Weekday per month for each Date in your load script, but haven't made up my mind yet.

Regards,

Stefan

[camilo]

Thanks Swuel,

I think is a bit simppler than that, I just want to compare for example monday of the fifth week of the year with monday of the fifth week last year. This in calendar month would be like comparing 1st Jan 2011 with 2nd Jan 2010 (a diference of 364 days).

Regards

[ToniKautto]

would work with the calendar table in your data model to flag the points in time you are interested in, like setting a flag for first monday in each month. Then you can utilize that in your expressions.

[swuehl]

Ok,

I understood that you have a Date as Input, than you want to derive the weeknumber and weekday of that and want to find the same weekday in weeknumber last year.

So you could use

{<Date={'=if(week(Date)=week(vCurrentDate) and weekday(Date)=weekday(vCurrentDate) and year(Date)=year(vCurrentDate)-1,1)'}>}

as set expression for use in a sum, if Date is you Date dimension and vCurrentDate ist a variable as BaseDate (or use today(), other Date value whatever appropriate).

For example

=only({<OrderDate={'=if(week(OrderDate)=week(vCurrentDate) and weekday(OrderDate)=weekday(vCurrentDate) and year(OrderDate)=year(vCurrentDate)-1,1)'}>} OrderDate)

gave me July 1st, 2008 as an OrderDate in a textbox if vCurrentDate is June 30th, 2009 (Both week 27, Tuesday within their years)

if you have a table with dimension date, you could alternatively use

=sum(if(week(OrderDate)=week(vCurrentDate) and weekday(OrderDate)=weekday(vCurrentDate) and year(OrderDate)=year(vCurrentDate)-1,Amount))

Is this what you want?

Have a nice evening

Stefan

[camilo]

Thanks Stefan, I´m going to try those examples, just one question do they work for time periods?

for example if I want to compare not just a day but a week or a month?

Thanks again,

Camilo

[swuehl]

Please look at my attached sample.

I noticed that my set expression will not really work if you select on Date.

I therefore created Week, Weekday etc. in my calendar in the script. Look also at the expressions in the text boxes, it its as well possible to limit the sum to weeks (and also month, if you add this to the data model).

Hope this helps,

Stefan

[camilo]

Thanks Stefan, I saw your example but I´m trying to compare time periods, not restricted to one week or a weekend, maybe 15 days or 8.

Here is an explample I created, maybe this help to clarify what I´m looking for

http://community.qlik.com/thread/33471

Thanks

Camilo