6 Replies Latest reply on Sep 3, 2015 12:14 PM by Rajeev Pandey

    Return date of max value

    Tom Barnes

      I am guessing that this is an easy one and I'm almost embarrassed to have to ask the question.  Unfortunately, I can't figure it out and I really need this to work.  So I am trying to find out what week the peak sales are within a time frame and return the date that it happened.

       

      So I have a calculation:  MAX([Sales]) which of course gives me my peak number but I need in the tooltip to give me what date this occurred.

       

      Any ideas?

        • 1. Re: Return date of max value
          Tracy Rodgers

          Hi Tom,

           

          Create a calculated field similar to the following:

           

          window_max(max([Sales]))

           

          Place this on the view. Depending how the view is set up, it may be necessary to edit how the calculation is being computed (to do this right click and select Compute Using...)

           

          Hope this helps!

           

          -Tracy

          • 2. Re: Re: Return date of max value
            Tom Barnes

            Hi Tracy and thank you for the response!  So what I need to do is have the max sales as shown but in the tooltip list the date that the max sales occurred.  So look at the example I attached using the sample database.  When I hover over the number "100,119" I would like it to show me the date that this occurred.

             

            Does that make sense?

            • 3. Re: Re: Return date of max value
              Tracy Rodgers

              Hi Tom,

               

              Got it. Create a calculated field similar to the following:

               

              if sum(Sales)=window_max(sum(Sales)) then max([Order Date]) end

               

              Place MDY([Order Date]) on the level of detail shelf. Then, place the above calculation on the level of detail shelf as well. Right click on it and select Compute Using...>Order Date. Right click again and select Filter... Un-check Null and click OK.

               

              Hope this helps!

               

              -Tracy

              • 4. Re: Return date of max value
                Tom Barnes

                And that is exactly what I was looking for!  You are awesome Tracy - Thank You!

                • 5. Re: Re: Re: Return date of max value
                  malou.puyod

                  Hi Tracy,

                   

                  I hope you can help me in my scenario.  I have the same scenario but I need to get the Peak Time or corresponding hour where I have the max/highest KPI during 24 hours in a day.  I was able to get the highest KPI value but I could not get the corresponding hour.  I tried using the solution you provided to Tom but I only get the max hour which is always 23:00 instead of the corresponding hour of the highest KPI.

                   

                  Hope you can help me.  Attached is the twbx for your reference.

                   

                   

                  Regards,

                  Malou

                  • 6. Re: Return date of max value
                    Rajeev Pandey

                    Dear Tracy mam,

                     

                    I am also looking for similar type of problem but in my case I want to know the "peak hour of each day". I want to create a graph that simply move from peak hour of one day to peak hour of the next day. (Here, First  I need to find the Peak hours of every day - Any idea how to do that). i need to create a graph in such a way as if the other 23 hours of the day don’t exist, which allows the user to focus exclusively on the peak hour that’s being presented.I need to create a graph for last 14 days.

                    For example :, X axis contains the Time (i. e start Time that contains data like 01.09.2015 23:15PM) and Y axis contains CPU Utilization and Line chart should move from one Peak hour to next peak till the 14th day Peak hour.

                     

                    Could you please suggest me how I can find the Peak hours of Every day and till the  last 14th Day.What calculation I need to use for achieving this scenario.