charting with generic week numbers

I need to chart multiple years with one line for each year and the axis showing the week numbers. I've been looking off and on, through the forum, for about four months and no luck finding a solution.  Please help.  Thanks.

Tagged:

Best Answer

  • "and asking me to post additional pictures seems like a typical IT delay tactic."

     

    this is a community, not Anaplan support. Nobody here is paid to answer and we're all doing it because we like the principle of a community. FYI some of the replies I made to this question I did them at 23h30 my time.

     

    The chart you have obtained is the best thing you can get in Anaplan. The X axies cannot have an additional tag and you cannot do overlay. on the values.

     

    With an additional week list, the only thing you can do is name it week 2 FY18 - FY17 but I don't think that brings much value. If you want to do that, you need to have a mapping between you additional time list and the real time and have that mapping in your data module.

Answers

  • Hi,

     

    this would be clearer if you were specifying which axies should have what.

    In any case it sounds like you need to have a module with the standard time as years and an additional list which recreates weeks (or the contrary) onto which you'll map your data.

  • Thanks so much for the response and I'll try to be more clear...you are headed the right way.   I want to chart gallons with two lines, one for current year, one for prior year.  The x axis = week #, y axis = gallons, user can chose site and activity.  four files attached...LIST:PNG =  I've created a list for week numbers like you suggested.  Module.PNG & module2.png:  a module with year as standard time...two views of module.  DATA.PNG = file where gallons are imported.  I'm stuck and cannot get my gallons data mapped into the module....this is probably basic stuff, but that is some of the stuff I struggle with...and I've been thru both modeling classes.Again thanks for the help...and is this a solution I could have found on the community....did I not look correctly?  Also is there additional reading I can do to understand mapping?

  • Hi Debbie

    If I understood correctly you have the data flowing by weeks for all the years.

     

    1) Current year should be loaded into Gallons and previous year data should be loaded into Gallons PY

    2) In your file you have to load the file twice one for Gallon and second for PY

    3) Make sure your names week columns match with the week dimension list items

    4) IF you could share your error file and data file it might be much easier to help you

     

    If the scenario is that you are getting previous year value from a file for this year and from coming year the previous year value comes from current year value present in Anaplan then you have to redesign your modules.

     

  • Thanks so much for the response.  I'll busy today with the weekly data load so may not get a chance to review until tomorrow , but I will get back to you.

  • So when I import my data I need to include a column for week number?  BTW I'm only going to import the file once and then I'll use LAG to get prior year.  Thanks for helping with this.

  • Hi,

     

    I think we have given you bad answers because we misundertsood your needs. First you expressed that you wanted a double time axis with years on X and weeks on Y. I don't think that's what you want.

     

    In your module with your timeline, you just need two line items Gallons and Gallons PY. Bring the numbers on Gallons and do a lag(Gallons,52 [if in weeks],0) for Gallons PY and that will do it.

  • you are correct...my issue is how to get the gallons to show up in module from the import module, please see pictures I posted earlier....how can i reference the week number when it's not in the import?  What is the formula?  sorry but this has been very frustrating for over a year now....I keep trying and setting it aside and trying  and setting it aside....anytime I've tried to map data from an import like this activity one...where row number is the dimension...i get error regarding no hierarchy.  but then sometimes it works and i find a way around it by adding yet another column to the data import.  It's sounding like that's the only way to map from modules with row numbers is by ever increasing the imported columns.I'm starting to doubt that this the right way to build...so that's why I'm attempting to get a better answer, again.  THANKS and I apoligize for my frustrated tone.  I do try to find my own answers on the forum but hours/days of looking and here I am.  I would have posted before but until the recent redo of the community I had not been able to find a place to post a question...and I have asked...just never got an answer.

  • Oh I see now !

     

    In your module activity numbers (the one with the rows), this is where you need to have a line item "week number" where you do item(week of year).

     

    Then on your "gallons" the formula should be: Activity Actuals Import.Gallons[sum:week number]

    That should bring you the data

     

    ps: your week number could actually be in the second module but it doesn't make much sense in terms of logic

  • I can see where me having "extra" fields trying to figure out how to make this work...could confuse.  BTW I removed the fields I didn't think you'd need to see. LOL

     

    Ok so how do I get:

    "In your module activity numbers (the one with the rows), this is where you need to have a line item "week number" where you do item(week of year)."  week of year is not a dimension, row numbers are

     

    by adding a field as part of the data prep

    OR is there some formula to bring it in from list = weeks of year?

    Thanks!

  • "week of year is not a dimension, row numbers are"

    then what is this time dimension we can see on top of that data module ?

  • time scale on activity data import module = week = i.e. Week 11 FY18

     

    "weeks of year" is the list I created per instructions below to have a generic "week Number"  (with no date)

     

  • Hi Debbie

     

    Could you please share us the file you are planning to load and further let us know the columns in the file you would like to load into Anaplan.

     

    What kind of information would you like to extract from the file including gallons and py numbers.

     

    I guess we need to remodify the design here based on what kind of info you would like ot load

  • I think you created that week list upon my instructions... however now I'm not sure you needed it.

    Why not having your second module directly over the real  time weekly timescale instead of a yearly timescale ?

     

    Otherwise you have to do a mapping between that week list and the real time and [SUM:on it]

     

    another question, why is you data loaded over a weekly timescale, is that required ?

  • if your two modules are on the same time dimension, you can simply make a reference to gallons without any formulas, providing your row list has a top level (you should have one)

  • sorry but what does this mean?

    providing your row list has a top level (you should have one)

     

    Also I was instructed to make everything that I've made for this example.

     

    I'm beginng to believe that the one way, and maybe only way to make Anaplan work is to add every possible field you need to your data import.  I've tried reading thru this complete thread, a couple of times, and I just get confused and when I ask a question it is not always answered....I appreciate that you all are trying to help me...but you're just confusing me....and asking me to post additional pictures seems like a typical IT delay tactic.

  • Hi Debbie the idea here was to understand your file and then see what kind of data you require and then help you with solution.
    Once we read through your file and understand what part of the data would you like in file we can give you better solution.

    Idea was to avoid giving multiple options and confuse you.

    If you need all the lines for transaction purpose and summarised data for reporting purpose we have methods of doing it. Most of the options are covered by Nathan and once we get more details we can select the option you need within this post
  • ok let's give this another try.

    Import file:

    Data.PNG

    I need this transaction data and then i need summarized data for reporting.

    I need a line chart with current year gallons and prior year gallons as seperate lines

    x axis = week

    y axis = current gallons, and prior year gallons

    Please let me know if you need more info....and please make the answer best practice...and simple so i can understand.  thanks for your patience....with my tired, and very old brain.

  • Hi Debbie

     

    Do we have option of having concantenated column to concantenate required columns to create a unique item in the CSV File you are going to import into Anaplan, use it as a code.

     

    The steps would be as below:

    1) Rather than using the week as list in the module you can create one more line item called week and it would be time formatted with week

    2) In the reporting module we can use sum function and sum the "gallons" by weeks

    3) Gallon PY can be achived by pointing it to the line item gallons and using the offset function

     

     

    Can make an example in the weekend and share across.

     

  • Oh my, an example would be fantastic.  Thanks.

  • Sorry but I still need an answer to this thread....thanks.

  • Ok let's try this again.  Here is the chart I've been able to create...however the time scale is not generic week numbers....so the prior year data labels reference the current year...it can be very hard for the user to see the data value and know which year the data belongs too.  That's why I took the approarch of setting up a list of "week numbers" to use in place of time scale...and I almost got it to work but can't get my data to map to the gerneric week numbers.  It seems like I'll have to tell my director that Anaplan CANNOT chart year over year and have accurate data labels.  I've spent another five hours searching the Anaplan community today, but cannot find an answer....plot 2018 values overlayed with the 2017 values.  Been trying to solve this on and off for over a year...guess EXCEL is still better at some things.  I hate having to make one chart in Excel....maybe we move all the charts back to EXCEL.  Can someone give me the true word....can this be done or not...and if so who can show me?  Sorry for my tone but I'm so frustrated that I can't get help....and lately every time I try and chat with support NO one is available.

    Chart.PNG

  • My sincere apoligies...I've seen answers from Anaplan employees so hoped they would participate.  David Smith answered my other question this week.  I also tried to contact Anaplan support, but no one has been available.

    My frustration got the better of me, and there is no excuse for my tone or words.  I appreciate the community aspect of this and the helpful nature of the members.  Thanks for calling me out!