Looking up Day of the Week list for a LI in a different module

neg177
neg177
in Modeling

Hi, 

I'm trying to be able to look up a total for all the individual days of the week from my historical data. i.e, I would need to look up all the Mondays in the Month of July and sum them together. I have weekdays as a list but I'm getting a error message when I try to use LOOKUP, SELECT, or SUM. Please advise. 

neg177_0-1658430660397.pngneg177_1-1658430688187.png

Blue Print 

neg177_2-1658430766837.png

neg177_3-1658430802948.png

 

 

 

Thank you 

Tagged:

Best Answers

  • ryan_kohn
    ryan_kohn
    Answer ✓

    I have a possible workaround for the issue you're running into by leveraging a Fake Time list for Days.

     

    In this example, I've built a list called Fake Time Day by importing a code for the Day and a mapping to a real time Day. Note that within Anaplan, there is no "Day" list format available, but you can use a date-formatted item in the same way (in majority of cases).

     

    My Fake Time Day list looks like this:

    ryan_kohn_0-1658443228997.png

     

    I also have System modules for attributes for native Days as well as Fake Time Days. See below for example from my Fake Time Days module. Note that I am doing a LOOKUP on the Real Date line item, which is date-formatted!

    ryan_kohn_1-1658443354375.png

    ryan_kohn_2-1658443390840.png

     

    In my example, I entered some dummy data in a module dimensioned by native Days:

    ryan_kohn_3-1658443455041.png

     

    And then I used a LOOKUP from my System module above to get that data into a module dimensioned by Fake Time Days:

    ryan_kohn_4-1658443503448.png

     

    When I do this, I'm able to use the attributes from Fake Time Days to get the result I'm looking for:

    ryan_kohn_5-1658443570945.png

     

     

     

    Hope this helps!

     

     

  • ryan_kohn
    ryan_kohn
    Answer ✓

    @neg177 Glad it worked for you! Can you mark my response as a solution? Thanks!

Answers

  • ryan_kohn
    ryan_kohn

    @neg177 Appreciate the blueprint and module views. For a question like this, it would be very helpful for you to share the formula you tried to write and what the error message was.

     

    Based on your description and module, it sounds like you want to SUM by Day Of Week and Month. You would need both these values stored at the Day level in order to SUM by them into your target module. I see also that you have Versions in your source model but not in your target module. You'll either need to SELECT a specific version in your formula or add Versions as a dimension in your target module, otherwise Anaplan will pick your Actuals version as the default.

  • neg177
    neg177

    Here is the message error I get when I put in the formula in this screen shot. 

    neg177_0-1658437748898.png

     

  • ryan_kohn
    ryan_kohn

    Okay, I tried replicate in my own model and was able to reproduce the error you are getting (note that I'm also SUMming by the Month in order to get the right value, as opposed to just the Day of Week).

     

    ryan_kohn_0-1658441046940.png

     

    I spoke with a couple colleagues and we believe that the formula should work. My suggestion would be to submit a case to support@anaplan.com.

     

    I will also think about possible workarounds.

  • neg177
    neg177

    We are trying this work around right now, but we have a quick question, how did you populate the Real Date LI? 

    neg177_0-1658518787213.png

     

    Thank you 

  • neg177
    neg177

     Your work around worked. Thank you very much

Categories