Create Calculated Column based on text 1

[datawrangler]

I need to create a calculated column or a measure that sums all Local Currency based on each company. All of our local currencies are in different fields in my data source, for instance:
"Company" "USD" "GBP" "JPY"
US $100 0 0
UK $190 £100 0
JP $100 0 ¥10,800

So you see, the problem is, our USD field not only includes sales of US in USD, it also contains our other companies sales in their local currencies converted at a static exchange rate. Whacky, I know.

Reason? I need to calculate a variance of total actual sales in local currency to forecast in local currency. My forecast table does have the information the way I need it.

So i I think I need a calculated column to say this:

where the company is like US Bring in "Dollars"
and where the company is like uk, bring in "GBP"
and where the company is like jp, bring in "JPY".

Thus creating one column of all sales in local currencies and eliminating the other company's sales at converstion rate from our 'Dollars" field. Am I just confused? ha.. thanks. I'm not a programmer so SQL statements are tough for me. thanks!

 

[datawrangler]

To clarify, I please replace the word Dollars with USD. Sorry, that's what I meant in the last sentence of my post.

 

[drlex]

DataWrangler
Could I ask you to post the Cognos application and the version number you are using? The solution will differ from Impromptu to ReportNet to Transformer. thanks,
lex

soi la, soi carré

 

[datawrangler]

Again, sorry. I'm am using Transformer 7.2 version 1. This is my model file.

 

[drlex]

Datawrangler
Can you not add a calculated column (call it local currency) to the IQDs that bring in sales figures with the logic you relate and then show it as a measure?
i.e.
IF Company = "US" THEN USD ELSE IF Company = "UK" THEN "GBP" ELSE IF Company = "JP" THEN "JPY"

The concern I would have is that the grand total for this measure would be a mix of currencies and hence useless in all views of the cube other than those that show (or filter on) the country dimension.

Perhaps you should look at the ability of transformer to do currency conversions - see the transformer pdfs in the documentation folder under cer3 that should have been installed along with the application (I can't recall whether it is step by step transformer or discovering transformer - they all have horrendous 8 letter filenames)

lex

soi la, soi carré

 

[datawrangler]

Putting in the Data Source is an idea I really hadn't thought of. The data source is a huge Access table. This is all temporary until we get our data warehouse built. I just thought there might be some sort of SQL statement I could run to give me that same info as a calculated column.

Thanks for the help