2 Replies Latest reply on Apr 19, 2018 1:15 AM by Prajval T

    Conditional Calculations

    Prajval T

      Hi Team,

       

      I have requirement like below.

       

      I need to show the Sales on bars and Sales %  on line as dual axes.

       

      Note: For Sales calculation i need consider previous 6 months data. (PFB for calculation for Sales %)

       

      Sales (%)= WINDOW_AVG(SUM([Sales]),-5,0)/WINDOW_AVG(SUM([Quantity]),-5,0)

       

      This is working fine.

       

      But if select 2014, it is showing all months data for Sales and Sales (%). But first

      5 months for Sales (%) will not applicable as it does not have previous 6 months data.

       

      Now my requirement is I need to show the all months data for Sales, but for sales (%) i wanted to show data from June 2014 which will make sense as per the logic.

       

      Right it showing from Jan.

       

      Can any one help me how to get this.

       

       

      Regards,

      Prajwal

        • 1. Re: Conditional Calculations
          Michael Hesser

          Hi Pravjval;

          Why not select the "Null if there are not enough values" in your Sale % table calculation?

           

          • 2. Re: Conditional Calculations
            Prajval T

            Hi Michael,

             

            it working fine for single line bar.

             

            But i have view something like below, Side By Side with line.

            Sorry i didn't mention complete requirement in my first post.

             

            Actual requirement is Green color is for Sales (%) with i have calculated using previous 6 months logic(As i mention in my first post) and Blue bar will indicates sum (sales), And The Goal (%) will be calculated as (sales)/ sales(%).

             

            So now i want to show is, i  need to show sales bars for all months, Sales (%) bar and Goal (%) line  should show from 6th month.

             

            For example i have data from 2017 Jan, and i have selected 2018 , April in year and Month filter then i need show data like below.

             

            Sales from Jan 2017 to 2018 April

            Sales (%) from June to 2018 April

            Goal (%) from June to 2018 April

             

             

             

             

             

            When i am trying to apply Null at table calculations, getting below error.