YoY/QoQ/MoM/WoW comparisons – UI design best practices
I've looked through a several topics on period-to-period comparisons, but most of them were about calculations, and none of them gave me clear hints on how to design the user interface if you want to be able to select any type of comparison on the same sheet, provided that broken weeks are allowed in the master calendar.
Preamble. Let’s say I have an application with sales data of a retailer with a wide network of stores. The application has at least 10 sheets. These sheets are organized in a logical way that helps you narrow down your search when trying to answer the questions “How have my sales changed in the current period compared to the previous one?” and “What were the key factors driving this change?”. This logic applies to any type of YoY/QoQ/MoM/WoW comparison. Basically it looks like this “Analyze n1 kpi at n2 level of details by store (region / store supergroup / group / subgroup / …) and n3 level of details by category (category supergroup / group / subgroup / product; there can be several category trees)”. Some trends are easier to analyze at specific levels of details, and such levels have to be fixed separately (so there can be quite a lot of sheets in an application).
The fiscal year begins on January 1 and ends on December 31, the accounting period is 1 month. Broken weeks are allowed in the master calendar.
Issue. Answers to the questions “How have my sales changed in the current period compared to the previous one?” and “What are the key factors driving this change?” must be provided on a daily/weekly/monthly/quarterly/yearly basis. Accordingly, I want to make it possible to select any type of comparison on each sheet. Each sheet has standard fields for selecting a period - year, month, week and date. The problem I'm addressing occurs when the user selects a year, a week, and that week is broken (for example, week 1 of the new year and the last week of the old year). If you use the fields associated with the ISO calendar, this problem disappears, but another problem arises - the selections for the year / month will not correspond to the accounting period.
I see 3 ways to deal with this problem, but none of them seems right.
Create 2 separate sheets with a set of fields related to a specific comparison type - one for YoY / QoQ / MoM comparisons, the other for WoW (I found this option in the topics I read).
Use set analysis. But then the simple formula "Sum(Sales)" turns into a complex construction "Sum({<ignore user selection by time fields, filter the set by year/quarter/month/week>} Sales)" and a potential conflict is possible when, for example, quarterly data is analyzed, and there is a need to drill down to the level of the week, while not going beyond the accounting period. Having two versions of indicators covering these use cases doesn't seem like a good option.
Use calculated fields and dimensions. For example, selecting WoW changes year, month, and week to ISO year, month, and week. But it is not entirely clear what to do if the user has already made selections for the fields of the standard calendar. Set a trigger that, when a week is selected, removes the selection from the month? But this won't help if the first or last week of the year is selected. Clear all selections from temporal fields and force user to reselect? Anyway, there are no triggers in Qlik Sense at all.
