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

    Replicating a Where condition / Wrongly using Exclude

    Daniel de Amilivia

      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:

      MonthExRateOfMonth
      2018010
      2018020
      2018030
      2018040
      2018050
      2018060
      2018070
      2018080
      2018090
      20181032.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!!