How to add last year and current year

Highlighted
New Contributor

How to add last year and current year

Hi

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.

tz9lawa_1-1595814122679.png

 

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

tz9lawa_2-1595814169835.png

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

  FY21FY20Variance
  1H2H  
  AUDmAUDmAUDm%
APA    
 PB    
 PC    
BPA    
 PB    
 PC    
CPA    
 PB    
 PC    
4 REPLIES 4
Highlighted
Valued Contributor

Re: How to add last year and current year

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]

Einas
"Give a Man a Fish, and You Feed Him for a Day. Teach a Man To Fish, and You Feed Him for a Lifetime"
Highlighted
New Contributor

Re: How to add last year and current year

thank you for your advice.

 

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

 

thanks

Highlighted
Master Anaplanner/Community Boss

Re: How to add last year and current year

@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

Misbah_0-1595840890656.png

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

Misbah_1-1595840964241.png

Misbah_2-1595841010683.png

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.

Misbah_0-1595841388826.png

Hope that helps

Misbah

 

 

Highlighted
New Contributor

Re: How to add last year and current year

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

tz9lawa_0-1595917461249.png

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      
                    
  FY21FY20Variance FY21FY20Variance FY21FY20Variance  
                    
SegmentProduct FamilyAUDmAUDmAUDm% ktktkt%AUDm AUD/tAUD/tAUD/t%AUDm  
APA2012+8.0+67% 81801+1%+0.14 246149+97+65%+8.4  
 PB1535-20.0-57% 20182+12%+4.12 7672,000-1,233-62%-24.1  
 PC1612+4.0+33% 3941-2-4%-0.45 410296+114+39%+4.5  
BPA8152+29.0+56% 23220+2%+0.95 3,5752,337+1,238+53%+28.0  
 PB2335-12.0-34% 81801+1%+0.41 283436-153-35%-12.4  
 PC6514+51.0+364% 20182+12%+1.65 3,322800+2,523+315%+49.4  
CPA8532+53.0+166% 3941-2-4%-1.21 2,180790+1,390+176%+54.2  
 PB1334-21.0-62% 23220+2%+0.62 5741,528-954-62%-21.6  
 PC6552+13.0+25% 81801+1%+0.61 800648+153+24%+12.4  
                    
           Variance Kt * FY20 AUD/T     Volume FY21kt * Variance AUD/t