3 Replies Latest reply on Dec 4, 2018 5:49 PM by swaroop.gantela

# Replicating a Where condition / Wrongly using Exclude

Greetings everyone,

Apologies for not being able to upload a workbook for easier understanding (company policies...).

Imagine I have this table:

MonthExRateSales Local CurrencySales USDSales USD (201810)
20180132.0510032053255
20180232.111035313580.5
20180332.1512038583906
20180432.213041864231.5
20180532.2514045154557
20180632.315048454882.5
20180732.3516051765208
20180832.417055085533.5
20180932.4518058415859
20181032.519061756184.5
20181132.5520065106510
20181232.621068466835.5

As you can figure, [Sales USD] = [Sales Local Currency] * [ExRate]

Problem is, that sometimes I want to see these values YTD, and for that I want to use a specific month's (I have a Parameter for selecting the month) ExRate.

So if Parameter = "201810", then I'd like [Sales USD] = [Sales Local Currency] * 32.5 //201810's ExRate

Here there are the ideas I came up with:

Create a calculated field (let's call it [ExRateOfMonth) like:

IF [Month] = [Parameter Month] THEN

[ExRate]

ELSE 0 END

That gives me this:

 Month ExRateOfMonth 201801 0 201802 0 201803 0 201804 0 201805 0 201806 0 201807 0 201808 0 201809 0 201810 32.5

Then I thought about excluding month, and getting the max ExRateOfMonth:

{ EXCLUDE [Month] : MAX([ExRateOfMonth]) }

But for some reason I'm not figuring out, it's not working: it returns the same as ExRateOfMonth

Since I don't want the user to constantly recalculating the whole extract, I don't want to put the [Parameter Month] on the query, so I created another datasource with the [Parameter Month] on the query, that just returns this value, but linking the main datasource with this one seems like a pain (because of the mix of aggr/non-aggr data).

If that's the easiest way, I'll handle the pain, but maybe there's an easier way like ExRateOfMonth approach.

Thanks!!

• ###### 1. Re: Replicating a Where condition / Wrongly using Exclude

Daniel,

I'm not sure if this quite got there, but maybe it can give ideas.

I think you had the right idea with the parameter.

You can fix the Selected Exchange Rate to the whole dataset by using:

{ FIXED :MAX(

IF [Month]=[Select a Month]

THEN [Ex Rate] END )}

//note no dimension declaration after FIXED

Then you can try something like this for the YTD:

IF ATTR([Month])=[Select a Month] THEN

WINDOW_SUM(SUM(

IF [Month]<=[Select a Month]

THEN [Sales Local Currency]*[Selected Exchange Rate]

END ) )

END

Replicating a Where condition / Wrongly using Exclude

1 of 1 people found this helpful
• ###### 2. Re: Replicating a Where condition / Wrongly using Exclude

Thank you very much!

I couldn't find the attached workbook, but this did the trick:

{ FIXED :MAX(

IF [Month]=[Select a Month]

THEN [Ex Rate] END )}

• ###### 3. Re: Replicating a Where condition / Wrongly using Exclude

Daniel,