Find average from earliest FY year to last year FY year ( excluding current FY Year)

[CK_WAKE]

Hi there,

I have a table as shown below, and I would like to calculate the average by dimension starting from the beginning of the earliest financial year to the last completed financial year (excluding the current financial year). An example is provided below.

I would greatly appreciate your assistance with this.

Thank you!

Product Faults	FY20	FY21	FY22	FY23	3 Year Avg. (excl FY23)
Desktop	10	20	17	1	16
Laptop	45	29	18	11	31
Phones	76	70	29	14	58

[G3S]

Avg({<FY={"$(=max(FY)-1)"}>}Product Faults)

https://help.qlik.com/en-US/sense/May2024/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/SetAnalysi...

https://help.qlik.com/en-US/sense/May2024/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/SetAnalysi...

[Padma123 · Accepted solution]

table:

CrossTable(financial_year,data,1)

load * inline [

product,FY20,FY21,FY22,FY23

desktop,10,20,17,1

laptab,45,29,18,11

phones,76,70,29,14

];

NoConcatenate

table2:

load *,

Date(Date#(MakeDate(20&Right(financial_year,2)),'DD/MM/YYYY'),'YYYY') as year ;

load *

resident table;

drop table table;

round(sum(aggr(sum({<data=e({<year={"$(=max(year))"}>})>}data),product))

/
aggr(count(distinct{<financial_year=e({<year={"$(=max(year))"}>})>}financial_year),product))

[CK_WAKE]

Thanks for the help.