1 Reply Latest reply on Jul 13, 2016 11:41 AM by Tom W

    Filtering based on 2 parameters

    galaxy.tab

      Hi All,

       

      I have 2 parameters in the attached workbook "Period" and "Compare Metrics".

       

      I want to filter the sales records in the table based on both these parameters.

       

      So, when the period filter values are selected i.e Month , Rolling 3 months and YTD the data has to change based on the parameter. The comparison metrics need to be applied as a comparison with MOM , YOY.

       

      So If Month is selected in Period filter and MOM is selected in Compare metrics then the value shown should be "Same year previous month value"

       

      Similarly if  Month is selected in Period filter and YOY is selected in Compare metrics then the value shown should be "Previous year previous month value"

       

      Another example is if rolling 3 months is selected and selection in compare metrics is MOM then it should do rolling 3 months for current month and previous 2 months. Again if YOY is selected it should compare with previous year rolling 3 months.

       

      Please advise on the above. Let me know in case of any questions.

       

      Thanks.

        • 1. Re: Filtering based on 2 parameters
          Tom W

          One approach is to build a calculated field such as;

           

          SUM (

          IF [parameterPeriod] = 'Month' and DATETRUNC('month',Now()) = DATETRUNC('month',[OrderDate]) THEN SALES

          ELSEIF [paramterPeriod] = ..........................

           

           

          )

           

          Build it out for each of your scenarios and layer in the logic for the 'compare' metric. I'm not really clear what you're trying to achieve with the compare metric though - do you need two columns one with the 'actual' and one with the compare, or should it just influence the calculation?