2 Replies Latest reply on May 31, 2012 4:36 AM by Brajesh Kumar

    How to calculate last day of data for any metric

    Brajesh Kumar

      I have one report with Day_Nbr, Metric Name and Metric Value. I am getting metric value for every metric based on selected date(as filter). Now, I also want to see Selected Date-1 day of data. I know I need to create a calculated field but how we can force it to bring selected date-1 day of data?

       

      Any help is greatly appreciated.

       

      Thank.

        • 1. Re: How to calculate last day of data for any metric
          Tracy Rodgers

          Hi Brajesh,

           

          Instead of using a filter, you can use a date parameter. Then, insert the dates from the Day_Nbr field. From here, a calculated field will be created that looks similar to the following:

           

          if [Date Parameter]=dateadd('day', 1, [Date Field]) then [Date Field] end

           

          Then, place this on the filter shelf, and check the Exclude box and Null. Finally, right click on the parameter and select Show Parameter Control.

           

          Hope this helps! Let me know if I've misunderstood the issue.

           

          -Tracy

          • 2. Re: How to calculate last day of data for any metric
            Brajesh Kumar

            Hi Tracy,

             

            I have attached one excel file to explain the requirement. My requirement is that I need to display value of the metric for the date(filter) selected by user and I am able to do this. With this I also need to display Previous day value of the metric along with selected date value and I am not able to achieve this.

             

            I am able to get Previous date by creating a calculated field with expression "max([FACT_Abc_DAY_NBR])-1" but not sure how to use this to get the metric value for this date.

             

            Thanks a lot for your help.