Previous Year in Bar chart

[Neha121]

Hi 

I have Below table where I have FY and contribution how do I get prev yr Contributions 

Financial Year	Contributions$	prev yr Contributions$
FY 2025/2026	10	20
FY 2024/2025	20	30
FY 2023/2024	30	40
FY 2022/2023	40	50
FY 2021/2022	50	0

I have created a table another table that has below data... but not sure rather than creating another table is there another smart way of achieving above table 

Financial Year	Previous FY Year
FY 2025/2026	FY 2024/2025
FY 2024/2025	FY 2023/2024
FY 2023/2024	FY 2022/2023
FY 2022/2023	FY 2021/2022
FY 2021/2022	FY 2020/2021

My main goal is 

• To create Combo chart 
• Bars to show sum of contributions $ (current and previous) over 5 FY 
• Line to show growth rate as = (latest yr $ - prev yr$) divide prev yr$

[ysalvi43]

Hello 
Please find below backend script based on your sample data 

Script:

Contributions:
LOAD * INLINE [
FinancialYear, Contributions
FY 2025/2026, 10
FY 2024/2025, 20
FY 2023/2024, 30
FY 2022/2023, 40
FY 2021/2022, 50
];

NoConcatenate

Contributions_Sorted:
LOAD
FinancialYear,
Contributions,
IF(IsNull(Previous(Contributions)), 0, Previous(Contributions)) as PrevYrContributions
RESIDENT Contributions
ORDER BY FinancialYear desc;

drop table Contributions;

Front end: Dimensions :  FinancialYear

3 measure 
Current Year: sum(Contributions) -- bar

Prev Year : sum(PrevYrContributions) -- bar

%: (Sum(Contributions) - Sum(PrevYrContributions)) / Sum(PrevYrContributions)  -- Line

Attaching screenshot for your refernece

Do let me know if any other part is missed

Thanks 

[Neha121]

this works when I just have one attribute(FinancialYear) in Contributions table however I have two more attributes. So the main table has

FinancialYear, Company, Department, Contributions

Company, Department are part of filters.

it doesnt work when I have multiple dimensions

[Neha121]

If I dont want 2 bars just 1 bar for current contribution and the line formula can I do it directly in Chart?

[ysalvi43 · Accepted solution]

Hello @Neha121 

The as we increase the granularity you will have to group by the dimension, so that when you filter the data as per company, department it should get filters out below is the script.

Here you will have to note that Sorting based on each dimension is the important step because if it is incorrect then the previous function will fetch incorrect value 

Script:

ContributionsRaw:
LOAD * INLINE [
FinancialYear, Company, Department, Contributions
FY 2025/2026, ABC Corp, Finance, 10
FY 2024/2025, ABC Corp, Finance, 20
FY 2023/2024, ABC Corp, Finance, 30

FY 2025/2026, ABC Corp, HR, 15
FY 2024/2025, ABC Corp, HR, 25
FY 2023/2024, ABC Corp, HR, 35

FY 2025/2026, XYZ Ltd, IT, 18
FY 2024/2025, XYZ Ltd, IT, 28
FY 2023/2024, XYZ Ltd, IT, 38
];

NoConcatenate

ContributionsAgg:
LOAD
FinancialYear,
Company,
Department,
Sum(Contributions) AS TotalContributions
RESIDENT ContributionsRaw
GROUP BY
FinancialYear,
Company,
Department;

NoConcatenate
ContribSorted:
LOAD
FinancialYear,
Company,
Department,
TotalContributions
RESIDENT ContributionsAgg
ORDER BY
Company, Department, FinancialYear DESC;

NoConcatenate

FinalContrib:
LOAD
FinancialYear,
Company,
Department,
TotalContributions,
If(Previous(Company)=Company AND Previous(Department)=Department,
Previous(TotalContributions)
) AS PrevYrContributions

RESIDENT ContribSorted;

DROP TABLE ContributionsRaw;
DROP TABLE ContributionsAgg;
DROP TABLE ContribSorted;

Attaching frontend screenshot where you can keep current contribution and %

Another step is by creating string between your dimension which all you requried, but those field/ dimesion should be present in both the tables.

Thanks