Comparing Quarters different years

[sosl11]

Hi,

What would be the best way to achieve the following:

User selects 2025-Q1, 2024-Q4, 2024-Q3, 2024-Q2

User wants to see the Sum(Sales)

But now User wants to compare it to  2024-Q1, 2023-Q4, 2023-Q3, 2023-Q2

This should also work when 2 or 3 Quarters are selected

2025-Q1, 2024-Q4, 2024-Q3 vs. 2024-Q1, 2023-Q4, 2023-Q3

2025-Q1, 2024-Q4 vs. 2024-Q1, 2023-Q4

Dataset:

SalesData:
LOAD
    Date(Date#(Date, 'DD/MM/YYYY')) as Date,
    Sales,
    Customer,
    Year(Date#(Date, 'DD/MM/YYYY')) as Year,
    Month(Date#(Date, 'DD/MM/YYYY')) as Month,
    'Q' & Ceil(Month(Date#(Date, 'DD/MM/YYYY')) / 3) as Quarter,
    Year(Date#(Date, 'DD/MM/YYYY')) & '-Q' & Ceil(Month(Date#(Date, 'DD/MM/YYYY')) / 3) as YearQuarter,
    Year(Date#(Date, 'DD/MM/YYYY')) - 1 & '-Q' & Ceil(Month(Date#(Date, 'DD/MM/YYYY')) / 3) as YearQuarterCompare
INLINE [
    Date, Sales, Customer
    01/01/2025, 22, 1
    02/01/2025, 33, 1
    01/02/2025, 44, 1
    02/02/2025, 55, 1
    01/03/2025, 66, 1
    02/03/2025, 77, 1
    01/04/2025, 88, 1
    02/04/2025, 99, 1
    01/01/2024, 91, 1
    02/01/2024, 92, 1
    01/02/2024, 81, 1
    02/02/2024, 82, 1
    01/03/2024, 71, 1
    02/03/2024, 72, 1
    01/04/2024, 63, 1
    02/04/2024, 64, 1
    01/05/2024, 54, 1
    02/05/2024, 53, 1
    01/06/2024, 43, 1
    02/06/2024, 42, 1
    01/07/2024, 31, 1
    02/07/2024, 32, 1
    01/08/2024, 21, 1
    02/08/2024, 23, 1
    01/09/2024, 12, 1
    01/09/2024, 13, 1
    01/10/2024, 101, 1
    02/10/2024, 103, 1
    01/11/2024, 113, 1
    02/11/2024, 114, 1
    01/12/2024, 125, 1
    02/12/2024, 126, 1
    01/01/2023, 133, 1
    02/01/2023, 134, 1
    01/02/2023, 143, 1
    02/02/2023, 144, 1
    01/03/2023, 154, 1
    02/03/2023, 155, 1
    01/04/2023, 166, 1
    02/04/2023, 167, 1
    01/05/2023, 178, 1
    02/05/2023, 179, 1
    01/06/2023, 181, 1
    02/06/2023, 182, 1
    01/07/2023, 191, 1
    02/07/2023, 192, 1
    01/08/2023, 67, 1
    02/08/2023, 69, 1
    01/09/2023, 79, 1
    01/09/2023, 78, 1
    01/10/2023, 89, 1
    02/10/2023, 83, 1
    01/11/2023, 73, 1
    02/11/2023, 74, 1
    01/12/2023, 65, 1
    02/12/2023, 64, 1
];

[Oleg_Troyansky]

Hi there,

I'll give you a line of thought that should bring you to the solution. Disregard the number of selected Quarters. Your users select a range of dates - hopefully the set of Quarters that they select is consecutive with no "holes" in between.

So, the "current period sales" calculation should include Set Analysis that selects the Date between the min available date and the max available date. Something along these lines:

Date = {"= (Date>=min(Date)) * (Date<=max(Date)) "}

Notice that I opted to use the Advanced (expression) Search rather than simple search, to be free from the date formatting issues.

Now, the "comparative period sales" calculation should be similar, but use the min date - 1 year and the max date - 1 year in the condition:

Date = {"= (Date>=AddYears(min(Date), -1)) * (Date<=AddYears(max(Date),-1)) "}

Come to my Set Analysis session at the Masters Summit for Qlik to learn more about advanced uses of Set Analysis.

[marcus_sommer]

You need only an appropriate running period-field which might be created with interrecord-functions or autonumber() or with a simple calculation like:

Year * 4 + Quarter as RunningQuarter

and then querying:

{">=$(=max(RunningQuarter)-4)<=$(=max(RunningQuarter)-4)"}

[sosl11]

Thanks for the suggestion, in this case the End user would select 2025-Q1 and 2024-Q4 for example which only works when using the YearQuarter field as it is two different years, so selecting Year 2024 and Year 2025 + Quarter Q1 + Quarter Q4 would result in 2025-Q1, 2024-Q1, 2025-Q1, 2025-Q4 instead of desired sum of Sales 2025-Q1 + 2024-Q4 which would then be compared to the sum of Sales 2024-Q1 + 2023-Q4. 

I tried out your suggestion but can not get the Date = {"= (Date>=AddYears(min(Date), -1)) * (Date<=AddYears(max(Date),-1)) "} to return me a result altho the Dates seems to be working: 

[sosl11]

Ah thanks that is also an idea, this would not working with my current Quarter field but would work with QuarterNr:

Ceil(Month(Date#(Date, 'DD/MM/YYYY')) / 3) as QuarterNr

I do unfortunately not get a result from the set expression:

[sosl11]

Ah that would work with:

Sum({
<YearQuarterNr=,
RunningQuarter = {">=$(=min(RunningQuarter)-4)<=$(=max(RunningQuarter)-4)"
}>
} Sales)

Lets see if that is sufficient.

Opens in new window

PDF Download

Word Download

Excel Download

PowerPoint Download

Document Download

[sosl11]

Hi Oleg,

Could you please elaborate a bit more on the Advanced Search? How can one output what comes out the Advanced Search (so the result)? Does that work in a text box somehow?

This works for Current Period Sales:

=Sum({<YearQuarterNr=, Date={"=(Date>=min(Date)) * (Date<=max(Date))"}>} Sales)

This does not return me the Comparative Period Sales:

=Sum({<YearQuarterNr=, Date = {"=(Date>=AddYears(min(Date), -1)) * (Date<=AddYears(max(Date),-1))"}>} Sales)

I would think it maybe has something to do within the Advanced Search string, but how do I check that? Thanks!

[Oleg_Troyansky]

Hi,

Advanced Search is simply an expression. It returns a number. Any number other than zero is considered as TRUE, and zero result is considered FALSE. 

On the surface, I can't see any issues with your expressions,  but I'd need to dive into the data and into the syntax to find out what's not working as expected...

[sosl11]

I got it to work with this now:

=Sum({<YearQuarterNr=,Date = {">=$(=Date(AddYears(Min(Date), -1)))<=$(=Date(AddYears(Max(Date), -1)))"}>} Sales)