#Data0 discussing

Hi @Jesse Mejaski and @Bhavana Neelagiri

The discrepancy you are seeing between the Forecasts Tab values, the actual Opportunity Record values, and Report values is a direct result of how Salesforce handles multi-currency exchange rates and two different rate types:

The Two Types of Exchange Rates in Salesforce

Salesforce uses two distinct mechanisms for currency conversion, which explains why your reports and forecasts show different values:

1. Static/Historical Exchange Rates (Used by Opportunities/Reports):
   • When an Opportunity is closed/won, Salesforce locks in the exchange rate that was active on the Close Date of that opportunity. This is the historical exchange rate.
   • The Amount field on the Opportunity record itself always displays the original currency amount, but converted currency fields in standard reports use this historical locked-in rate. This ensures revenue numbers don't change retroactively every month.
2. Advanced Currency Management Rates (Used by Collaborative Forecasts):
   • The Forecasts Tab uses Advanced Currency Management (ACM) rates (the monthly adjusted rates entered by your finance team). These are the current and dynamic exchange rates.
   • When you view an open (pipeline) Opportunity in the Forecasts tab, Salesforce converts that amount using the current effective exchange rate for that month's forecast period, not the historical rate.

Why the Values Are Different in the Forecast Tab vs. Reports/Records

• Opportunity Record/Standard Report View: Shows values converted using the historical exchange rate that was active on the close date (or today's rate if still open). These values are static.
• Forecasts Tab View: Shows values converted using the current, dynamic ACM exchange rate that your finance team updated this month. These values change monthly as rates are updated.

The system is behaving exactly as designed to provide a dynamic "pipeline value right now" view in forecasts, versus a static "actual recorded revenue value" view in closed opportunities and reports.

How to Ensure Consistency (If Required)

If your goal is to have all views show the exact same amount, regardless of the dynamic month-to-month changes in the forecast, you have two primary options:

1. Align Reporting Currency: Ensure all users view reports in a single, common corporate currency (e.g., USD) using the Converted Currency field in reports, relying on the locked-in historical rate.
2. Stop Monthly Rate Adjustments for ACM: If you want forecasts to match historical reports exactly, your finance team would need to stop adjusting the exchange rates monthly in the Advanced Currency Management settings, but this is rarely a desired business outcome.

So, the calculation is modified only slightly to account for the = or # characters.

REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE([Unclear Data],CHAR(10),''),'(\d{4}:)',''),'<.*?>|&.*?;|[=]|[#]',' ')

And it returns this when I modified the original data set:

As to usage of CHAR(10) and '(\d{4}:) - you have at least one occurrence where there's a carriage return in the string but not all of the strings have them. CHAR(10) is the code that represents a carriage return. So, the first (most inner) REGEXP_REPLACE looks for that carriage return (which is not visible) and eliminates it so that the end part of the calculation works. For the capture group of (\d{4}:) in the second REGEXP_REPLACE it is used to remove those 4-digit (not letters) instances that you had in the very first row of the data example. There were a number of items like 1234: or 5678: etc. The capture group looks for just 4 digits, starting from the colon and then removes them. The very last REGEXP_REPLACE, the outer one, is doing all the heavy lifting. In Regex, the pipe | symbol is an 'or' operator. So that last piece '<.*?>|&.*?;|[=]|[#]' has four different patterns for the regex engine to look for and eliminate. The first one looks for HTML tags, the second one looks for any string values between the & and ; the last two are your most recent need and it's just simply [=] | [#] Hope that helps! Best, Don

Hi Sheetal,

Your code looks great! My guess would be that you are clicking on the headers on your worksheet instead of clicking on the actual marks on your worksheet, i.e. clicking on "Tables" instead of clicking on the bar. Try clicking on the mark itself and see if you get data. If that's the case then know that this will be fixed in 2020.3.

Take care,

Keshia

Hi HongKai,

I think it needs nested LOD expression to count pair users per month.

<formula>

Initial Char (this determines pair of points.)

LEFT([Point], 1)

# of Pair

{FIXED [test_date], [Initial Char], [User ID]: IF ZN(COUNTD([Point])) = 2 THEN 1 ELSE 0 END}

# of Pair User

{FIXED DATETRUNC('month', [test_date]): COUNTD({FIXED DATETRUNC('month', [test_date]), [User ID]: IF SUM([Pair]) > 0 THEN MIN([User ID]) END})}

<sample -- focused on Sep 2018>

Please check attached workbook.

Hi Shaleen,

You are selecting the correct refresh option. A limitation of Tableau Public is that all of the data must be extracted, therefore there is only one refresh option.

My next suggestion would be to edit the data connection and verify that the workbook is pointing to the correct Excel file.