7 Replies Latest reply on Jun 21, 2016 11:32 AM by Joshua Cloud

    Comparing Date of Two Date Ranges / Time Periods

    Ashwin Chandak

      Hello,

       

      I have a metric X that has date from the last 6 months.

       

      I want to have the ability to let the user select two date ranges (say, one is from 6/13 to 6/20 and the other is form 5/10 to 5/17) and I want to calculate the % difference in that metric between the  two time periods.

       

      Is there a way I could do so?

       

      I know there is a lookup() I could make use of.

       

      Unfortunately I cannot provide a date source or packaged workbook and am trying to explain in the best way possible.

       

      Any workarounds would be great! Thanks a ton!

       

      Ashwin

        • 1. Re: Comparing Date of Two Date Ranges / Time Periods
          Joshua Cloud

          Ashwin,

           

          I have been able to do this with the use of Parameter controls. I will use the Superstore Sample data as my example.

          First you will need to create 4 parameters:

          Beginning and End of each range segment:

          Once you have all four assigned to their date ranges, you can select "show parameter control" by right clicking on the parameter. This populates them on the right hand of the screen:

           

          Now we need to tie the parameters into our calculations. I have created 2 calculation, one for each date range:

          You can change the greater than or less than signs as you see fit.

          I also created a % difference calculation (may be different from the one you will use):

           

           

          And finally we can drop these calculations into our view.

           

           

          Let me know if this isn't what you are looking for.

           

          Cheers!

          3 of 3 people found this helpful
          • 2. Re: Comparing Date of Two Date Ranges / Time Periods
            Ashwin Chandak

            Thanks Josh. I have some questions:

             

            1. You are using a list parameter for your ranges, thus, the dates won't refresh everyday. Is is possible to attain the same functionality with a 'All' parameter instead of a list one?

            2. I am getting the following when I followed through the steps you provided:

             

             

            I had to add 'Date' to the columns for the else it provides only nulls.

             

            Is there a way, I could aggregate the values for the defined periods rather than the individual values by days?

             

            Thanks again!

             

            -Ash

            • 3. Re: Comparing Date of Two Date Ranges / Time Periods
              Joshua Cloud

              Ashwin,

               

              1. Yes, using an 'All' versus a 'List' would work as well, the list is what worked for me.

               

              2.  Can you provide a view of your calculations? I am noticing that your % Diff calculation is still providing nulls even with the date added to the columns. I am thinking that it has something to do with how you are aggregating your Value 1 and Value 2 calculations.

               

              Cheers!

              • 4. Re: Comparing Date of Two Date Ranges / Time Periods
                Ashwin Chandak

                Hi joshua,

                 

                Thanks for your promptness. I have managed to get the nulls out from % Diff:

                 

                Here are my Value 1, Value 2, % Diff Calculations:

                 

                Value 1: if attr([Date])>= [Date Range A Begin] and attr([Date])<= [Date Range A End] then [Score] END

                Value 2: if attr([Date])>= [Date Range B Begin] and attr([Date])<= [Date Range B End] then [Score] END

                % Diff: (ABS([Value 1]-[Value 2])/[Value 1])*100

                 

                The difference between your calculation and mine is that I have had to use attr() for my date as seen above otherwise it would give me a 'Cannot mix non-aggregate and aggregate functions' error.

                 

                I checked the date data type and its the same as yours.

                 

                So I am left with two issues:

                 

                1. Attr() for the dates

                2. Values not updating with a change in parameter dates, Value 1 and Value 2 is populating only for the common dates

                3. Need to place the day(date) pills in columns and hence am getting the day grain for the values

                 

                 

                Looking forward to hearing from you. Thanks again!

                 

                Ashwin

                • 5. Re: Comparing Date of Two Date Ranges / Time Periods
                  Joshua Cloud

                  Hmm, I see where the issue may come from:

                   

                  When I replicate your aggregation, I also get nulls. So the next question I have is, what is your score calculation? We may need to change where its aggregation occurs to remove the Attribute calculation, because it doesn't work without the Date level of detail in the view.

                  • 6. Re: Comparing Date of Two Date Ranges / Time Periods
                    Ashwin Chandak

                    I see.

                     

                    I have two firleds: Numerator and Denominator:

                    and Score is a ratio of the two fields, namely, (SUM([Measure Numerator])/SUM([Measure Denominator]))*100

                     

                    Is the aggregate field causing the issue?

                     

                    Thanks!

                     

                    -Ash

                     

                     

                     

                    • 7. Re: Comparing Date of Two Date Ranges / Time Periods
                      Joshua Cloud

                      Try updating your Value 1 and Value 2 calculations to:

                       

                      Value 1: 

                      SUM( IF [Date] >= [Date Range A Begin] AND [Date] <= [Date Range A End] THEN [Measure Numerator] END )

                        /

                      SUM( IF [Date] >= [Date Range A Begin] AND [Date] <= [Date Range A End] THEN [Measure Denominator] END ) * 100

                       

                      Value 2:

                      SUM( IF [Date] >= [Date Range B Begin] AND [Date] <= [Date Range B End] THEN [Measure Numerator] END )

                        /

                      SUM( IF [Date] >= [Date Range B Begin] AND [Date] <= [Date Range B End] THEN [Measure Denominator] END ) * 100

                       

                      Try these calculations without the [Date] pills in your columns.

                       

                      Let me know if it doesn't work.

                       

                      Cheers!