topic Lining up dates between 2 years in graphs in QlikView

Lining up dates between 2 years in graphs

kdmarkee — Fri, 15 Nov 2024 23:54:53 GMT

I'm wondering if there is a way to better line up dates between years. When I want to show trends by comparing 2 years, if I do it at the day of week level of granularity, the current year obviously won't line up with the previous year. The attached helps explain it. I want to show the full 7 days of last week from this year and also the same full week last year. I have flags set in my calendars to bucket my dates and then use those flags in set analysis.... ie, LastWeek={1} and LastWeek_LastYr={1}. Maybe the way I built my flags is incorrect. Maybe I'm missing something else here? This looks particularly bad/odd because the weeks shown here are Aug 31-Sep 6 for 2020 and Aug 26-Sep 1 2019, because that is how the this year vs last year lined up.

Re: Lining up dates between 2 years in graphs

edwin — Sat, 26 Sep 2020 02:55:53 GMT

The way i would associate different time periods is by using a bridge. i would associate different years by a weekIndex (the numerical number of the week in the year) and a Day number (Monday=0, etc).
i would associate the current year with the prior year using these two dimensions this way:

NoConcatenate tmpPriorYear: //this gets the dates its day, week, and year load Date, DayNum, WeekIndex, Year Resident Calendar ; inner join (tmpPriorYear) //inner join it with itself (cartesian join) //on DayNum and WeekIndex across years load Date as PriorDate, DayNum, WeekIndex, Year as PriorYear Resident tmpPriorYear ; Concatenate (Bridge) load Date, DayNum as WeekDayNum, WeekIndex as xWeekIndex, Year as xYear, PriorDate as FactDate, PriorYear, 'PRIOR' as DateType Resident tmpPriorYear where PriorYear=Year-1 //this filters for only prior years //other rows are discarded ; drop table tmpPriorYear;

Re: Lining up dates between 2 years in graphs

kdmarkee — Thu, 01 Oct 2020 13:46:44 GMT

Thanks for the suggestion. I will try it and let you know how it goes.