1 Reply Latest reply: Dec 7, 2017 1:30 PM by Felip Drechsler RSS

    User date selection + YTD calc + fixed max date how?

    rashid muhammad

      Hi All

      I have a situation where I need to generate pivot table see pic below. When user select any period I need to calc YTD in the Set Analysis I have to have max of SEPN date. I dont know how to write this statement. when I say SEPN ={$(=max(Rpt Yr))} I get 2017 only because Rpt Yr 2016 has no 2017 in SEPN.

       

      Please help

      Requirement:

      yr2.PNG

      Sample Data: xlsx attached

      yr1.PNG

        • Re: User date selection + YTD calc + fixed max date how?
          Felip Drechsler

          Hi Rashid,

           

          See the following QVW for reference.

          I just transformed the date into numeral because it's easier to handle in set analysis.

           

          So, if the user gets any date of the month for a specific year, lets say February 2nd, it will get the sum of count field for the year of 2017 and where the month is YTD based on selection.

          In this case, it will sum up all the values for January and February 2017.

           

          Left most table is the generated calendar, mid table is the excel data and right most table is the expression used to calculate with set analysis,

          Sample.png

          Felipe.