Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
CK_WAKE
Creator
Creator

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

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

 

 

 

 

 

 

1 Solution

Accepted Solutions
Padma123
Creator
Creator

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

View solution in original post

3 Replies
Padma123
Creator
Creator

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
Creator
Creator
Author

Thanks for the help.