charting with generic week numbers

debbie5154

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.

nathan_rudman

"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.

nathan_rudman

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.

debbie5154

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?

harish_bk

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.

 

debbie5154

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.

debbie5154

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.

nathan_rudman

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.

debbie5154

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.

nathan_rudman

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

debbie5154

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!

nathan_rudman

"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 ?

debbie5154

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)

 

harish_bk

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

nathan_rudman

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 ?

nathan_rudman

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)

debbie5154

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.

harish_bk

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

debbie5154

ok let's give this another try.

Import file:

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.

harish_bk

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.

 

debbie5154

Oh my, an example would be fantastic.  Thanks.

debbie5154

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

debbie5154

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.

debbie5154

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!