Possibility of calculating YTD figures based on MTD data using Rul

CA Chaitanya Shirsul
CA Chaitanya Shirsul Active Partner
First Anniversary Level 200: Building A Planning Solution in Board Level 100: Foundations of Building in Board First Comment
edited November 2023 in Platform

Hi All,

I want to explore the possibilities of calculating YTD figures using MTD data using a rule.

For example -

Revenue for Jan 2023 - 1000

Revenue for Feb 2023 - 1000

Using Rule can we calculate and store it under new member called "YTD Revenue"

YTD Revenue for Jan 2023 - 1000

YTD Revenue for Feb 2023 - 2000

Using procedures or layouts this can be done easily, wanted to see if any option available in Rule to try this out.

Tagged:

Answers

  • Tommaso Riva
    Tommaso Riva Employee
    Fourth Anniversary 25 Likes 5 Answers 10 Comments
    edited November 2023

    Hi Chaitanya,

    The Yearly Cumulated Value is the best feature to achieve your goal, it returns the cumulated value from the beginning of the current year.

    Moreover, the time functions can be executed in a dataflow step.

    The Rule is never dynamic by definition.

    Why are you looking for Rule adoption in this case?

    Regards,

    Tommaso

  • CA Chaitanya Shirsul
    CA Chaitanya Shirsul Active Partner
    First Anniversary Level 200: Building A Planning Solution in Board Level 100: Foundations of Building in Board First Comment

    We have a requirement where whenever user updates the MTD figures, YTD should get updated and stored as a separate line item in the same layout immediate next row.

    Using time functions this can be done, but needs to flow each line items YTD data separately.

    Example - Select "EBITDA (YTD)" step → Data flow from block a (Apply block reference to EBITDA in Main cube) to Block b (Main cube)

    We have proposed another alternative where we simply show YTD figures in a separate layout using time function but user prefer MTD and YTD view in the same layout as below.

    image.png

    Do you suggest any flexible alternative to this requirement.

    Regards,

    Chaitanya

  • Tommaso Riva
    Tommaso Riva Employee
    Fourth Anniversary 25 Likes 5 Answers 10 Comments

    Hi Chaitanya,

    I would try to explore the Period by Period recursion flag on Dataflows.

    To be able to use this option the following requirements must be met:

    1. the source and target cube are the same.
    2. the source Block is set to period offset of -1.

    In this way the result of the Dataflow for the first period is used as the source for the following one.

    Please check it out and let us know if it can be useful to you.

    Regards,

    T.

Categories