3 Replies Latest reply on Feb 8, 2019 11:15 AM by Tim Dines

    Plz Help me on Logic to implement below scenario

    Jilani Shaik

      Hi

      I have a chart like quarter wise sales and profit for each year and one filter called 'Year' filter. when I select particular year it will show that related year quarter data

       

      I need a logic where Past year sale to be merge into current year quarter Q1 and future sale into current year quarter Q4.

       

      Note : Logic should apply only to sale in that sheet but not profit.

      2018-Q3
      2018-Q2
      2018-Q3
      2018-Q4
      2019-Q1
      2019-Q2
      2019-Q3
      2019-Q4
      2020-Q1
      2020-Q2
        • 1. Re: Plz Help me on Logic to implement below scenario
          Ken Flerlage

          I'm not following you. Can you provide a sample data set or workbook and give us an idea of the expected output?

          • 2. Re: Plz Help me on Logic to implement below scenario
            Jilani Shaik

            Hi

             

            Plz see above chart I have year,quarter wise Profits and Sales.

             

            Need logic how to implement

            like 2014 complete sales should add into 2015_Q1 Sales

            and 2016 & 2017 complete sales should add into 2015-Q4 sales

             

            Plz help me on how to implement

            • 3. Re: Plz Help me on Logic to implement below scenario
              Tim Dines

              Since we don't have your twbx file to show you the solution in, I will try to describe how this is done.  Since you are not asking for the same number of quarters before and after I would create two parameters one for the number of quarters before the quarter and one for the number of quarters after a given date.  You will also want a parameter to choose the starting year/quarter combination.  You will need two calculated fields.  One for the quarters before the selected year/quarter that contains a Lookkup function that  says PriorQ >= Lookup(sum([sales]),-x) where X is the number of quarters to go back, chosen from the first parameter.  The second calculated field is much like the first but will have a positive number for X (N), AfterQ <= Lookup(sum([sales]),-N).