Importing data with dates and multi-currencies

ClarenceAndre_*44012

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 Items (alternative view)Data ImportImport with FailuresImport with Failures Details

Accepted answers

ChrisAHeathcote

@ClarenceAndre

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

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

All comments

ChrisAHeathcote

@ClarenceAndre

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

ashish.banka

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

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

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

Hope this helps.

ClarenceAndre_*44012

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

210326_Import_with_Failures_103108.jpg

210326_Import_with_Failures_Details_103108.jpg

ashish.banka

@ClarenceAndre 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.

ClarenceAndre_*44012

Dear @ChrisAHeathcote

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

210326_Import_with_Failures_Details_2_103127.jpg

ClarenceAndre_*44012

HI @ashish.banka

That is indeed the case as we have consultant names, project names, billing rates and currencies. I was able to reduce the error rate significantly by doing two things: (i) removing Project Name from the line item as its already a dimension and (ii) re-instating Time as a dimension. That said we still have an issue as if each week we can only have one currency but in reality consultants may be billing in different currencies, that causes a conflict so those records will get rejected in the import process.

I guess either we find away around this in Anaplan or fix the issue at source by billing in only one currency.

Best regards,

Clarence

ClarenceAndre_*44012

Dear @ashish.banka @ChrisAHeathcote

Think I figured the issue. Seems to be data quality at source where some people are keying in billing rates in a currency other than what it should be hence the conflict. The currency should have been the same per consultant per project and if not obviously results in an error when importing.

That said because the billing rates are rolling up into totals for the week (in reality doesn't make sense), I need to divide it by the number of records in that week. Is there a formula in Anaplan that counts number of data records in any given week?

Kind regards,

Clarence

ChrisAHeathcote

@ClarenceAndre

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;

Chris

ClarenceAndre_*44012

Many thanks @ChrisAHeathcote 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

ChrisAHeathcote

@ClarenceAndre

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

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

ClarenceAndre_*44012

Many thanks Chris! That's really helpful, thanks so much!

Quick Links

Can you help?

Unanswered questions

import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…