Importing data with dates and multi-currencies

ClarenceA
Regular Contributor

Importing data with dates and multi-currencies

Hi please refer the following attached screenshots:

210325 Line Items

210325 Line Items (alternative view)

210325 Line Items (Data Import)

210325 Import with Failures

210325 Import with Failures Details

 

Situation:

To import data from a flat file comprising dates, names and billing rates in different (foreign) currencies e.g. AUD, JPY, SGD and so on. The plan is to convert them to USD (our reporting currency) but first I need to import the raw data. I initially had the dates in Date format and the currencies Line Formatted but converted both to text as Anaplan didn't seem to like different currencies being associated with the same date. I gather from the failure log that Anaplan is expecting only one currency to be associated with each date but the actual situation requires us to handle multicurrency situations where a person could be billing to more than one projects on any given day in different currencies.

 

Question:

So how does one go about doing this? Because of the failures I tried different approaches like removing the Time dimension and converting dates to text rather than Date. I additionally removed the Line Formatting on the Local Currency line item but neither of this works as well.

 

Look forward to advice on how we resolve this.

 

Kind regards,

ClarenceLine ItemsLine ItemsLine Items (alternative view)Line Items (alternative view)Data ImportData ImportImport with FailuresImport with FailuresImport with Failures DetailsImport with Failures Details

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHeathcote
Community Boss

Re: Importing data with dates and multi-currencies

@ClarenceA 

Yes, there is a simple way to enable the summary items to calculate the correct billing rate. 

Use the ratio summary option for both lists and time.

As the Total = rate x hours then rate = total / hours

Therefore, we can set the summary option to a ratio of Total Billable / Total Hours

 

ChrisHeathcote_0-1616752557761.pngChrisHeathcote_1-1616752586344.png

 

Another option is to use average summary option but you will get slightly different answers and average will not take account of variations in hours or rate in the same way ratio will.

 

I would recommend ratio as a summary option.

 

Chris 

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA

View solution in original post

11 REPLIES 11
ChrisHeathcote
Community Boss

Re: Importing data with dates and multi-currencies

@ClarenceA 

Is there a reason why you have not created a list for currency and are not using date as a dimension.

I would create two currency lists; base currency and target currency.

Add the base currency list to your data import module along with date. This will allow you to allocate more than one currency to a date.

The reason I have included target currency is so that you can create a calculation in which you are able to translate the base currency value into any target currency. 

Just ensure that the module used to hold the rates includes your base currency and target currency list.

Chris 

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA
ClarenceA
Regular Contributor

Re: Importing data with dates and multi-currencies

Dear @ChrisHeathcote 

Appreciate your reply. Am new to Anaplan with limited real world experience on the same so kindly pardon my questions.

"I would create two currency lists; base currency and target currency": my target currency is USD which is a line item in the module. For each record with billable hours it should be a straightforward task to convert local currency to USD by looking up an exchange rate table in a sys module.

"Add the base currency list to your data import module along with date. This will allow you to allocate more than one currency to a date": yes that's what am doing.

That said I'm still not sure how we deal with two or more currencies in any given week as there's obviously a conflict when rolling up into a week - see attached screenshot.

Any ideas how one gets past that?

Kind regards,

Clarence

 

 

ChrisHeathcote
Community Boss

Re: Importing data with dates and multi-currencies

@ClarenceA 

I would recommend some changes to how you are importing your data.

 

Firstly, I would like to recap the challenges you are facing.

From the screenshots you have supplied so far I can see that you are including the Project name and PS Team Member lists as dimensions. 

The purpose of the import is to collect data on billable time for each PS Team Member. 

You have noticed that some PS Team Members are being booked to a project in the same week using more than one currency. 

The current import process is returning errors as the data contains duplicates across the dimension that you are currently using. 

 

My suggestion

1. Add a time dimension to Time Data module

2. Create a currency list and add this Time Data module ( call it Base Currency as 'Currency' is reserved for system use )

 

This will allow you to import data across all Projects, PS Team Members, Currency and dates. Therefore, you should not have any duplicate records being created. 

 

Below are some screenshots of how I would structure the module;

 

ChrisHeathcote_2-1616749677532.png

 

ChrisHeathcote_1-1616749582260.png

 

Chris 

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA
ClarenceA
Regular Contributor

Re: Importing data with dates and multi-currencies

Many thanks @ChrisHeathcote for  your detailed reply. Very helpful. I've restored the Time dimension like you've pointed out so works better now although still a few errors but they are likely data quality related as a person should be billing in a consistent manner to a project. For example Consultant X based in JP should bill in JPY rather than JPY at times and other currencies at other times. This variability was causing issues when aggregated at the weekly level. That said, I had not considered using Day as a time unit. In fact I wasn't even aware I could thinking that Week was the smallest unit of time so many thanks for that revelation!

The other point I noted Chris in your reply was I wasn't sure of the need for totals as the hours would at any rate aggregate up to the week. The issue of course is the same would also apply for billing rates which wouldn't make sense. I am thinking to fix that to include a column of 1s in the source data so that when aggregated to week serves as a count for number of records falling in that week. Unless there's a better method for doing. I'm rather limited by my vocab of Anaplan formulas and looking through the list of available formulas didn't quite find what I needed. I'm sure there's a better way!

Kind regards,

Clarence

ChrisHeathcote
Community Boss

Re: Importing data with dates and multi-currencies

@ClarenceA 

Yes, there is a simple way to enable the summary items to calculate the correct billing rate. 

Use the ratio summary option for both lists and time.

As the Total = rate x hours then rate = total / hours

Therefore, we can set the summary option to a ratio of Total Billable / Total Hours

 

ChrisHeathcote_0-1616752557761.pngChrisHeathcote_1-1616752586344.png

 

Another option is to use average summary option but you will get slightly different answers and average will not take account of variations in hours or rate in the same way ratio will.

 

I would recommend ratio as a summary option.

 

Chris 

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA

View solution in original post

ClarenceA
Regular Contributor

Re: Importing data with dates and multi-currencies

Many thanks Chris! That's really helpful, thanks so much!
ashish.banka
Super Contributor

Re: Importing data with dates and multi-currencies

@ClarenceA  I tried replicating your file example and was able to load data without any failures

 

 

Community_1.jpg

 

Here are my Module Line item settings (Note: I assumed date in DDMMYYY format in the file)

Community_2.jpg

 

I guess... while loading raw data from file, you can look at Data line item to include "Date" format and use custom mapping to map the format of the data in the file

Community_3.jpg

 

Hope this helps.

 

AB

ClarenceA
Regular Contributor

Re: Importing data with dates and multi-currencies

Dear @ashish.banka thanks very much.

I'm still not able to get it to work (pls refer attached error screenshots) despite switching the date format back to Date and even customizing the mapping per your screenshot below. The dates are in mm/dd/yyyy format but that doesn't matter as we can select the appropriate date format.

It's good that you managed to get it to work with 12 records. The actual dataset is over a 1,000 records which I unfortunately can't share due to potentially sensitive info - as you can see I've erased names and billing rates.

From the error log, almost all errors have a similar pattern; here's an example: "More than one value for non-numeric cell: 1/5/2021 and 2021-01-04". I deliberately took out the time dimension so that the module doesn't try to roll-up into a week or higher time interval (Calendar Type is Weeks) so am not sure why this is happening. For avoidance of doubt, there are indeed multiple records for any given date as we're compiling time records of people working on various projects in different currencies so its clearly the interplay of this that's stuffing up the import. Grateful for further advice.

Kind regards,

Clarence 

ashish.banka
Super Contributor

Re: Importing data with dates and multi-currencies

@ClarenceA Looks like you have duplicates in the file. I mean you might be having multiple records for the same "Name" in the file which might be causing the error. Ideally when we load raw data from any external source or file, we need to create a Unique ID for each record, then first create Unique ID list and then load raw data in the module with Unique ID list as "Applies To". 

Hope this helps.

 

AB