Find value from other line item and populate it backwards

johan.marketoft
johan.marketoft
in Modeling

Hi, 

 

Module:

Dimension: Time (Months)

Line item: Amount, Proforma (both Number format)

 

The Amount line item is imported based on imported forecast. I then want the Proforma line item to populate the first found value from Amount for all cells in current FY. 

 

Ex.

Amount is 100 from August to Dec 2020.

FY starts in Jun 20, so I then want the Proforma line item to get value 100 in all cells during FY, so from Jun 20 to May 21.

 

Best regards,

Johan

Tagged:

Best Answer

  • rolympia
    rolympia
    Answer ✓

    Consider a Summary Method solution. Here our goal is to put a value in the FY* of the Time dimension by manipulating the Summary Method of Line Items, and using Yearvalue to take it and put it in the months. Likely, your Summary Method is set to be Summary or Average, which is likely what you don't want. Consider 2 new line items:

    > One if Nonzero: IF Amount <> 0 then 1 else 0 : Summary Method: SUM

    > Nonzero Average: Amount / One if Nonzero : Summary Method: FORMULA

    The Formula for Proforma: Yearvalue(Nonzero Average)

     

    So this is a solution based on a face-value treatment of your current data (nonzero average of 100 for Aug to Dec = 100), but you'll have to flesh out how you want to treat any data as they come in. What happens when values are not all the same? Is this Nonzero Average the correct treatment? Or do you want the earliest or latest inputted value?

    *I assume that in your time setting your Fiscan Start Month was changed from the default Jan to June

     

    Hope this helps!

Categories