Compare same day to same day last year (monday with monday)

[amien]

As title describes, i would like to compare days with days previous year. Based on the dayname (monday).

In a threat on this forum i found this load script line:

date(if(weekday(date)-weekday(AddYears(date, -1))>0, addyears(date, -1) +(weekday(date)-weekday(AddYears(date, -1))), addyears(date, -1)+(7-weekday(date)-weekday(AddYears(date, -1))))) As [SameDay-1]

This will give me an extra field in my calender.

But how can i use this? i don't quite see it.

How would my expression look like in a pivot?

Thanks in advanced

[swuehl · Accepted solution]

Not sure if I understood how your field calculation should work for your comparison.

You can consider using an AsOf table, potentially only using these specific relation:

The As-Of Table

Something like

ASOF:

LOAD DateField AS AsOfDate,

          DateField,

          'Current' as Type

RESIDENT YourMasterCalendar;

LOAD Date( DateField + 7*52) as AsOfDate,

          DateField,

          'Last Year' as Type

RESIDENT YourMasterCalendar

WHERE Exists(AsOfDate, Date(DateField + 7*52)) ;

[amien]

Thanks swuehl

AsOf is indeed much easier

[RafaelLucasdaCosta]

Hi @swuehl , I know it's an old post but I'm not understanding how this table works and how I can use it in my charts, could you explain? Thanks in advance

[benvatvandata]

Is there anyway to achieve this without scripting? i.e. using advanced expression in chart?