4 Replies Latest reply on Dec 18, 2016 5:57 PM by Anna Leveriza

# Previous Value: Get the last value of the previous year

Hi,

I have a dataset and my goal is to get the last cost of the previous year as my beginning cost.

I'm using the following formulas:

First Cost = IF [Ship Date]=[First Ship Date]THEN [Min Cost] ELSE NULL End

Last Cost = IF [Ship Date]=[Last Ship Date]THEN [Min Cost] ELSE NULL End

Base Cost =

IF [First Cost]<>0 THEN [First Cost]

ELSEIF [Last Cost]<>0 THEN [Last Cost]

ELSEIF [First Cost]=NULL AND [Last Cost]=NULL then NULL end

Beg. Cost = IF ATTR([Base Cost])<>NULL then ATTR([Base Cost]) ELSE PREVIOUS_VALUE(ATTR([Base Cost]))END

Edit Table Calculation:

Compute Using: Addressing > Product, Date .... Sort Automatic

At the Level: Date

Restarting Every: Product

And the result will be the beginning cost is the first cost of the year or the first base cost.

What I want to happen is something like this:

I think it's a matter of changing either the the Beg. Cost formula, Base Cost formula or Editing the Table Calculation but I just don't know what to tweak in order to arrive at my goal. Hope you could help. Thanks!!!

Message was edited by: Anna Leveriza

• ###### 1. Re: Previous Value: Get the last value of the previous year

Hi Anna

Could you attach packaged workbook. (***.twbx)

Thanks,

Shin

• ###### 2. Re: Previous Value: Get the last value of the previous year

Hi Anna,

Thanks,

Dhruv

• ###### 3. Re: Previous Value: Get the last value of the previous year

Hi Anna,

I'm not entirely clear on what you are trying to do with firstcost, lastcost and basecost but I think you can get what you want using just mincost which seems to have a value for all rows.

I used the following formula:

IF IFNULL(LOOKUP(MAX(IF [Date] = { FIXED [Year] : MAX([Date]) } THEN [Min Cost] END),-1 ) , PREVIOUS_VALUE(0) ) = 0 THEN ATTR([Min Cost])

ELSE IFNULL(LOOKUP(MAX(IF [Date] = { FIXED [Year] : MAX([Date]) } THEN [Min Cost] END),-1 ) , PREVIOUS_VALUE(0) ) END

Regards,

Ivan

• ###### 4. Re: Previous Value: Get the last value of the previous year

Hi Ivan,

Your solution is possible if I only have 1 product. The problem will come when computing for Beg. Cost of Product B. It will just copy 0.54 or the last cost of Product A.