Conflicting Rules?

      Conflicting Rules?

      Hi there

      I am attempting to use rules to apply Exchange Rates to all Financial data points in my cube ("Benchmarks" cube), based on rates that are stored in another cube ("Exchange_Rates" cube)

      Rule 1 (Constant) derives the value in USD by dividing any value in the cube where Currency is VEF(Venezuelan Bolivars) by the matching Currency in the Exchange_Rates cube where the Sub Category is "Max" (latest quarter)

      Rule 2 (Variable) derives the value in USD by dividing any value in the cube where Currency is VEF(Venezuelan Bolivars) by the matching Year/Quarter/Currency in the Exchange_Rates cube regardless of Sub Category

      These two rules appear to work in isolation. However, when I introduce more rules where the only difference is the specified currency e.g. GBP, then only the first rule of type (Constant) or (variable) works, depending the order in which they appear in the Rule editor

      For example, if I have "Variable VEF Rule" first and "Constant GBP Rule" second, they both work, if i have "Constant VEF Rule" first and "Constant GBP Rule" second and "Variable GBP Rule" third - only "Constant VEF Rule" and ""Variable GBP Rule" work?

      Are the "constant" versus "constant" and "Variable" versus "Variable" rules conflicting? surely not or is it just the order I've got them in?

      The structure of the cubes is as follows

      Benchmarks

      [Service][Scenario][Region_Country][Year][Quarter][Device Type][Operator][Type][Category][Sub Category][Currency][Frequency][Model_Input][Calc_Y_N][Measure]
      WOSLVenezuela2014Q1N/AMovistar VenezuelaN/APrepaidVariable Exchange RateUSDMonthlyComponent 1YRevenue

      PUK2015
      Q2SmartphoneEEVoicePostpaidConstant Exchange RateVEFQuarterlyComponent 2NARPU

      W
      2016
      Q3

      Non-VoiceTotalN/AGBPAnnuallyComponent 3
      Subscriptions



      2017
      Q4

      Data

      EURN/AN/A
      Churn



      2018


      Messaging

      KRW






      2019





      SEK






      2020





      N/A





      Exchange_Rates

      [Year][Quarter][Type][Sub Category][Currency]
      2014Q1BidN/AUSD
      2015Q2AskMaxVEF
      2016Q3Midpoint
      GBP
      2017Q4

      EUR
      2018


      KRW


      Rules in Benchmarks

      Source Code

      1. ​['Sub Category':'Constant Exchange Rate','Currency':'USD'] = ['Sub Category':'N/A','Currency':'VEF'] / PALO.DATA("SAS_Dashboards","Exchange_Rates","All Years","All Quarters","Midpoint","Max","VEF");VEF CONSTANT;;1464186101
      2. ['Sub Category':'Variable Exchange Rate','Currency':'USD'] = ['Sub Category':'N/A','Currency':'VEF'] / PALO.DATA("SAS_Dashboards","Exchange_Rates",!'Year',!'Quarter',"Midpoint","WOS","VEF");VEF Variable;;1464186924
      3. ['Sub Category':'Constant Exchange Rate','Currency':'USD'] = ['Sub Category':'N/A','Currency':'GBP'] / PALO.DATA("SAS_Dashboards","Exchange_Rates","All Years","All Quarters","Midpoint","Max","GBP");GBP CONSTANT;;1464194173
      4. ['Sub Category':'Variable Exchange Rate','Currency':'USD'] = ['Sub Category':'N/A','Currency':'GBP'] / PALO.DATA("SAS_Dashboards","Exchange_Rates",!'Year',!'Quarter',"Midpoint","WOS","GBP");GBP Variable;;1464194223




      Any help much appreciated. Even if to tell me I'm barking up the wrong tree and cannot be done

      Thanks

      J