topic Re: Comparing Quarters different years in App Development

Comparing Quarters different years

sosl11 — Fri, 18 Apr 2025 09:15:56 GMT

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

Re: Comparing Quarters different years

Oleg_Troyansky — Fri, 18 Apr 2025 16:19:02 GMT

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.

Re: Comparing Quarters different years

marcus_sommer — Tue, 22 Apr 2025 11:04:35 GMT

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)"}

Re: Comparing Quarters different years

sosl11 — Tue, 22 Apr 2025 15:36:11 GMT

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:

Re: Comparing Quarters different years

sosl11 — Tue, 22 Apr 2025 15:40:11 GMT

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:

Re: Comparing Quarters different years

sosl11 — Thu, 24 Apr 2025 10:19:56 GMT

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

Re: Comparing Quarters different years

sosl11 — Wed, 30 Apr 2025 07:23:17 GMT

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!

Re: Comparing Quarters different years

Oleg_Troyansky — Wed, 30 Apr 2025 15:37:51 GMT

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...

Re: Comparing Quarters different years

sosl11 — Wed, 07 May 2025 14:02:17 GMT

I got it to work with this now:

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