Aggregated Average and Comparison to Last Year with dummy data

SamuliPeura — Wed, 29 Nov 2023 12:34:35 GMT

I want to calculate:

Rolling 13 Months Average of Type=a / Rolling 12 Months Sum of Type=b

This works just fine in a KPI like this:

avg(
{<YearMonth,Month)>}
aggr(sum({<[Type]={a},
Date = {">$(=AddMonths(MonthEnd(Max(Date)),-13))<=$(=Max(Date)) "}
,YearMonth,Month)
>} [Amount])
,YearMonth,Month))

/

Sum({<[Type]={b},
Date = {">$(=AddMonths(MonthEnd(Max(Date)),-12))<=$(=Max(Date)) "}
,YearMonth,Month
>} [Amount])

But I would like to display it in a Bar Chart, where the dimension is Month with another measure that calculates the same things but Year-1.

So for example month 5 in the bar chart should show for:

measure 1: 5/2022-05/2023 divided by 6/2022-05/2023

measure 2: 5/2021-05/2022 divided by 6/2021-05/2022

Any ideas? Thanks in advance!

I have created "Dummy Data Script" so you can work it out on your own Qlik Sense.

NoConcatenate
Dummy:
Load * Inline [
YearMonth, Month, Amount, Type, Date
202101, 1, 10, a, 01.01.2021
202101, 1, 11, b, 01.01.2021
202102, 2, 12, a, 01.02.2021
202102, 2, 13, b, 01.02.2021
202103, 3, 14, a, 01.03.2021
202103, 3, 15, b, 01.03.2021
202104, 4, 15, a, 01.04.2021
202104, 4, 16, b, 01.04.2021
202105, 5, 17, a, 01.05.2021
202105, 5, 18, b, 01.05.2021
202106, 6, 18, a, 01.06.2021
202106, 6, 16, b, 01.06.2021
202107, 7, 17, a, 01.07.2021
202107, 7, 289, b, 01.07.2021
202108, 8, 28, a, 01.08.2021
202108, 8, 25, b, 01.08.2021
202109, 9, 27, a, 01.09.2021
202109, 9, 25, b, 01.09.2021
202110, 10, 27, a, 01.10.2021
202110, 10, 38, b, 01.10.2021
202111, 11, 49, a, 01.11.2021
202111, 11, 36, b, 01.11.2021
202112, 12, 25, a, 01.12.2021
202112, 12, 25, b, 01.12.2021
202201, 1, 10, a, 01.01.2022
202201, 1, 11, b, 01.01.2022
202202, 2, 12, a, 01.02.2022
202202, 2, 13, b, 01.02.2022
202203, 3, 14, a, 01.03.2022
202203, 3, 15, b, 01.03.2022
202204, 4, 15, a, 01.04.2022
202204, 4, 16, b, 01.04.2022
202205, 5, 17, a, 01.05.2022
202205, 5, 18, b, 01.05.2022
202206, 6, 18, a, 01.06.2022
202206, 6, 16, b, 01.06.2022
202207, 7, 17, a, 01.07.2022
202207, 7, 289, b, 01.07.2022
202208, 8, 28, a, 01.08.2022
202208, 8, 25, b, 01.08.2022
202209, 9, 27, a, 01.09.2022
202209, 9, 25, b, 01.09.2022
202210, 10, 27, a, 01.10.2022
202210, 10, 38, b, 01.10.2022
202211, 11, 49, a, 01.11.2022
202211, 11, 36, b, 01.11.2022
202212, 12, 25, a, 01.12.2022
202212, 12, 25, b, 01.12.2022
202301, 1, 10, a, 01.01.2023
202301, 1, 11, b, 01.01.2023
202302, 2, 12, a, 01.02.2023
202302, 2, 13, b, 01.02.2023
202303, 3, 14, a, 01.03.2023
202303, 3, 15, b, 01.03.2023
202304, 4, 15, a, 01.04.2023
202304, 4, 16, b, 01.04.2023
202305, 5, 17, a, 01.05.2023
202305, 5, 18, b, 01.05.2023
202306, 6, 18, a, 01.06.2023
202306, 6, 16, b, 01.06.2023
202307, 7, 17, a, 01.07.2023
202307, 7, 289, b, 01.07.2023
202308, 8, 28, a, 01.08.2023
202308, 8, 25, b, 01.08.2023
202309, 9, 27, a, 01.09.2023
202309, 9, 25, b, 01.09.2023
202310, 10, 27, a, 01.10.2023
202310, 10, 38, b, 01.10.2023

];

exit script;

Re: Aggregated Average and Comparison to Last Year with dummy data

F_B — Tue, 10 Sep 2024 10:05:09 GMT

Hi @SamuliPeura ,

maybe something like this:

// Rolling 13-Month Average for Type=a (Previous Year)
Avg(
Aggr(
RangeSum(
Above(Sum({<[Type]={'a'}, YearMonth={"$(=Date(AddYears(Max(Date),-1),'YYYYMM'))"}>} Amount), 0, 13)
) / 13,
YearMonth
)
)
// Divided by Rolling 12-Month Sum for Type=b (Previous Year)
/
Sum(
Aggr(
RangeSum(
Above(Sum({<[Type]={'b'}, YearMonth={"$(=Date(AddYears(Max(Date),-1),'YYYYMM'))"}>} Amount), 0, 12)
),
YearMonth
)
)

Hope this helps

topic Re: Aggregated Average and Comparison to Last Year with dummy data in App Development

Aggregated Average and Comparison to Last Year with dummy data

Re: Aggregated Average and Comparison to Last Year with dummy data