Add formula to a Has Data? Line item

Hello,

 

I am new to Anaplan platform. Now I am working on Level 2 model building training. Here I have an issue to write a formula to line item. I don't have any kind of data in SYS13 Account Filter Module. I have data in DAT03 Historic Volumes Module. In DAT03 Module I have only one line item Volumes and I don't have any List items which are rolled-up to their parent. All the lists are flat lists only. Here how can I write a formula to check particular item has data or not.

Ajaykumartm1_0-1613162452907.png

 

Answers

  • @Ajaykumartm1 

    Great question. This is part of the L2 certification to test your ability to use Anapedia (for a hint on this problem, click this link). 

    It's best to get familiar with Anapedia. Even the best of the best Master Anaplanners come back to Anapedia. 

    Give it click. See if that points you in the right direction.

    Also, use the search feature in Anaplan Community. You'll find that someone has probably asked the same question. Nothing wrong with seeing how others solved the problem, as long as it helps you get the right answer!

  • I have followed the process in the link you provided. But its not working. That's I posted the question here.

  • @Ajaykumartm1 

    Can you share a screenshot of your formula? 

    Let's get you to the finish line!!

  • Hi,

     

    I can able to write formula by using MONTHVALUE() function for my issue. This is ok now for my requirement. But I have another query here. I got the values from source to target by using MONTHVALUE() function, My source has Month's as a time scale and target has Week Time Scale (4-4-5). If I transfer data same month value is loaded for all the weeks in target. 

    For example: I have 4 as a value for January month in source module, When I transferred data to target, I have the values like Week1 (4), Week2 (4), Week3 (4), Week4 (4). I don't want values like this, I want the values to be segregated when it comes to target like, In my target January month has 4 weeks if I transfer the value it should split value of 1 for each week and the total value of Jan is 4. But now 4 value is added to each week and my consolidation value of Jan is 16. 

     

    How can we segregate the values from months to week. I searched in google and I am getting only functions to aggregate the value not for segregation.

     

    Can you please help me on this.            !!Thanks in Advance!!

  • @Ajaykumartm1 

    Gotcha! Let me address both points.

    • MONTHVALUE() is a very creative way to solve the problem, but there is an easier way and that is to use TIMESUM() Your formula should look something like this:

    Has Data? = TIMESUM('DAT03 Historic Volumes'.Volumes) <> 0

    • Regarding your second question. Two ways.

     

    1. Here's how I would approach the problem 
      • Start by creating a system module for weeks
      • Add a numeric line item with a formula that equals "1"
      • Add another system module for months
      • Sum up the weeks from the weekly system module. This will give you the week count for each month
      • Then in your monthly planning worksheet create a line item that divides your monthly value by the week count
      • In your weekly planning module refer to the line item you created above.
    2. Use Breakback logic
      • Turn on breakback for the line item. When you edit the monthly value it will disaggregate to the weeks.
  • @JaredDolich @Ajaykumartm1 

     

    If you are going to use timesum(), make sure the line item with the timesum formula does not have Time applied to it.  In this instance, you will be creating a subsidiary view, but this is better for performance purposes.

     

    Rob

  • @rob_marshall thank you for your comment! I had the correct formula but had my time set to week/FY19. I'll remember that for next time 🙂 @JaredDolich , I totally agree! Anapedia is awesome 📚

  • Hello Jared,

    You describe all the points where I'm facing error.

    After reading your comment, I understand all point properly.

    Thank You so much for help.