Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
amien
Specialist
Specialist

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

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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)) ;

View solution in original post

4 Replies
swuehl
MVP
MVP

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
Specialist
Specialist
Author

Thanks swuehl

AsOf is indeed much easier

RafaelLucasdaCosta
Contributor II
Contributor II

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
Partner - Creator II
Partner - Creator II

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