2 Replies Latest reply on Aug 15, 2018 6:30 AM by Vijayraj Pediredla

    Dynamic Filters on Dimension Based on two Measure Fields

    Vijayraj Pediredla

      Hi Leads,

      I was trying out the following scenario.

       

      Dimension:

      Customer Name

       

      Measures:

      M1 = LastYear_Net_Sales

      M2= CurrentYear_Net_Sales

       

      Parameter:

      Drop down with the list of values = Active, Last_Year_Active, Not_Active

      Formula  :

      Active= CurrentYear_Net_Sales > 0

      Last_Year_Active = (LastYear_Net_Sales > 0 and CurrentYear_Net_Sales = 0)

      Not_Active = (LastYear_Net_Sales = 0 and CurrentYear_Net_Sales = 0)

       

      Output Requirement:

      A stacked bar chart with LastYear_Net_Sales vs CurrentYear_Net_Sales amounts along with Customer Name who could be either one of them(Active, Last_Year_Active, Not_Active)

      Parameter controlled with (Active, Last_Year_Active, Not_Active)

       

      Source Data Set

      Customer NameLastYear_Net_SalesCurrentYear_Net_sales
      Customer 1235,555$405,444$
      Customer 2225,444$
      Customer 3445,325$
      Customer 4
      Customer 5

       

      Issues with Design output:

      1. When  "Active" value is selected Under the parameter, I see proper results as the condition is on one column that is

      Formula= CurrentYear_Net_Sales > 0,

      Output

      CustomerNameLastYear_Net_SalesCurrent...
      Customer 1235,555$405,444$
      Customer 3445,325$

      Calculated Customer Field.: Customer_Active

      If (CurrentYear_Net_Sales > 0)

      then Customer Name

      else 'Filter'

      End

       

      2. When "Last_Year_Active" value is selected Under the parameter, I see incorrect results.

      Formula = (LastYear_Net_Sales > 0 and CurrentYear_Net_Sales = 0)

      Output

      CustomerNameLastYear_Net_SalesCurrent...
      Customer 1235,555$405,444$
      Customer 2225,444$

      Note:

      a. I'am not suppose to get the Customer 2 based on the condition.

      b. I have even replaced the Nulls with 0 but still the same result.

      Calculated Customer Field.: Customer_LastYear_Active

      If (LastYear_Net_Sales > 0 and CurrentYear_Net_Sales = 0)

      then Customer Name

      else 'Filter'

      End

       

      3. When "Not_Active" value is selected Under the parameter, I see incorrect results.

      Formula =(LastYear_Net_Sales = 0 and CurrentYear_Net_Sales = 0)

      Output

      Customer Name    LastYear_Net_Sales       Current.....
      Customer 1
      Customer 2
      Customer 3
      Customer 4
      Customer 5
      Filter   460999$850769$

      Note:

      a. I'am suppose to get the Customer 4,5  based on the source data set.

      b. I have even replaced the Nulls with 0 but still the same result.

      c. I see all the customers when I place the Final calculated field on the Row bar and the totals are assigned to Filter ( else part)

      Calculated Customer Field.: Customer_Not_Active

      If (LastYear_Net_Sales = 0 and CurrentYear_Net_Sales = 0)

      then Customer Name

      else 'Filter'

      End

       

      Final Calculated field which is dropped on the Row Bar.

      Customer_Active_Inactive

      if Parameter = 'Active' then Customer_Active

      elseif Parameter = 'LastYear_Active' then Customer_LastYear_Active

      elseif Parameter = 'Not_Active'   Customer_Not_Active

      End

      So this parameter is used to show the desired output.

       

      Kindly suggest me.

      Vijay