How to add last year and current year

tz9lawa

I have monthly data by segment by product family. - Refer to document attached

I would like to build dashboard to display last year, current year and variance column.

Therefore, i build 1 module, source, last year, current year and variance line item

Question:-

How to input formula to grab data for FY20?

How to input formula to grab data for FY21?

Beside year, i also need half year data

		FY21	FY20	Variance
		1H	2H
		AUDm	AUDm	AUDm	%
A	PA
	PB
	PC
B	PA
	PB
	PC
C	PA
	PB
	PC

CM_by_Year_and_Half_Year_78206.xlsx

Accepted answers

Misbah

@tz9lawa

If you don't want to write the formulae then you have to have Time Scale in your Target module as well ( but at year level) and then refer the data from source module. Even if you do that you will not be able to calculate variance without formulae

It will be something like this

But as @einas.ibrahim suggested you simply create a module without Time and use LOOKUP to pull value and then find the delta between the two. Below module FYR

To your second question if you want to get Half yearly values as well you have to enable that aggregation first in your Time settings and create two additional line items or howsoever you wish to publish those.

Hope that helps

Misbah

All comments

einas.ibrahim

Hi @tz9lawa

I’m assuming you have the data in other data modules dimensiones by Time.

Since you have time as a dimension then you can just add the numeric value you wish to display as a line item and the data for each year will just be available without any special formula. If you add Half Year to the time setting (if it’s not selected ) then you will automatically have values by half year as well. Target module should have Time Scale of Half Year, this way you will have half and full year.

If for a specific reason you really need to have a formula to retrieve data by a specif time, then you can create a System module that has 2 line items, current year and prior year (use a formula to calculate these values)

in your main module you can reference the current year values by a formula like ...

source_module.value[LOOKUP:system.current year]

tz9lawa

thank you for your advice.

Without formula added to last year and current year, how to calculate variances?

thanks

Misbah

@tz9lawa

It will be something like this

But as @einas.ibrahim suggested you simply create a module without Time and use LOOKUP to pull value and then find the delta between the two. Below module FYR

Hope that helps

Misbah

tz9lawa

as per advice, i manage to build module to cater current year and prior year data.

Using subset.

with this blueprint setting, are we able to use formula to calculate column Volume AUDm and CM/t AUDm?

	FY21 vs. FY20
		Contribution Margin					Volume						CM/t

		FY21	FY20	Variance		FY21	FY20	Variance		FY21	FY20	Variance

Segment	Product Family	AUDm	AUDm	AUDm	%		kt	kt	kt	%	AUDm		AUD/t	AUD/t	AUD/t	%	AUDm
A	PA	20	12	+8.0	+67%		81	80	1	+1%	+0.14		246	149	+97	+65%	+8.4
	PB	15	35	-20.0	-57%		20	18	2	+12%	+4.12		767	2,000	-1,233	-62%	-24.1
	PC	16	12	+4.0	+33%		39	41	-2	-4%	-0.45		410	296	+114	+39%	+4.5
B	PA	81	52	+29.0	+56%		23	22	0	+2%	+0.95		3,575	2,337	+1,238	+53%	+28.0
	PB	23	35	-12.0	-34%		81	80	1	+1%	+0.41		283	436	-153	-35%	-12.4
	PC	65	14	+51.0	+364%		20	18	2	+12%	+1.65		3,322	800	+2,523	+315%	+49.4
C	PA	85	32	+53.0	+166%		39	41	-2	-4%	-1.21		2,180	790	+1,390	+176%	+54.2
	PB	13	34	-21.0	-62%		23	22	0	+2%	+0.62		574	1,528	-954	-62%	-21.6
	PC	65	52	+13.0	+25%		81	80	1	+1%	+0.61		800	648	+153	+24%	+12.4

											Variance Kt * FY20 AUD/T						Volume FY21kt * Variance AUD/t

Quick Links

Can you help?

Unanswered questions

Automating Flat File Uploads to Anaplan Data Orchestrator (ADO) – Best Practices and Methods
Hello Anaplan Community, I am seeking guidance on the most effective ways to automatically upload flat files to Anaplan Data Orchestrator (ADO). Currently, our process involves loading flat files to an Anaplan Datahub using Anaplan APIs and custom Python scripting. With Anaplan Data Orchestrator's capabilities for data…
How to Design Simulation Planning in Anaplan with Multi-Level User Input and Step-by-Step Logging?
Hi Anaplan Community, I'm working on a simulation planning model where users need the flexibility to input pricing adjustments at any level of the product hierarchy, and I’d love your guidance on best practices. 📌 Scenario Overview: We have a 4-level product hierarchy: Brand Category Sub Category SKU (lowest level) The…
OAUTH 2.0 vs Google Sheets Extension
Hi, My org is looking to explore leveraging Google Apps script to extract data out of Anaplan and update already built templates in Google Sheets. We use the Anaplan Extension currently but find it crashes often with the amount of tabs, data, etc. Has anyone used both Anaplan OAUTH 2.0 API and Goolge Apps Script to write…