4 Replies Latest reply on Dec 27, 2018 8:04 AM by Prasad N

    Sort view by Last 2 weeks sales difference

    Prasad N
    Prasad N Dec 25, 2018 9:48 AM

      Hi

       

      I have to sort the view by 'Sub-Category'. If the difference between Last week and Current week sales is greater then that Sub-Category should be First in sort order and if the difference between two weeks sales is lowest than that Sub-Category should be placed in the last.

      I tried to it by creating below 3 calculated filelds and sort by Sales Diff.

       

      Curr Week Sales= IF [Order Date]=DATEADD('week',0,[Latest Week])then [Sales] end

       

      Previ Week Sales= IF [Order Date]= DATEADD('week',-1,[Latest Week]) then [Sales] end

       

      Sales Diff= [Curr Week Sales]-[Previ Week Sales]

       

      But 'Sales Diff' field values are not being calculated, kindly advice why values are not there and how to sort the view based on last two weeks sales difference

       

      Attached the workbook for your reference.

       

      Thanks,

      Prasad

        • 1. Re: Sort view by Last 2 weeks sales difference
          Don Wise
          Don Wise Dec 25, 2018 10:45 AM (in response to Prasad N)

          Hi Prasad,

          Please see attached 10.2 workbook; lowest versioning that we can go for exporting version as (hopefully opens for you).  Please follow along:

           

          Changed [Curr Week Sales] to a Fixed LOD:

          Screen Shot 2018-12-25 at 10.33.57 AM.png

          Did the same thing for [Previ Week Sales]:

          Screen Shot 2018-12-25 at 10.34.10 AM.png

          Changed [Sales Diff] to the following (needed to use SUM and ROUND):

          Screen Shot 2018-12-25 at 10.34.20 AM.png

          Then selected Sub-Category pill and clicked on Sort:

          Screen Shot 2018-12-25 at 10.34.27 AM.png

          A new dialog box opens.  Use the drop-down and selected sort on [Sales Diff]:

          Screen Shot 2018-12-25 at 10.34.38 AM.png

          Sort order is now High-to-Low based on Sales Difference for most current week:

          Screen Shot 2018-12-25 at 10.35.22 AM.png

          Screen Shot 2018-12-25 at 10.42.35 AM.png

          • 2. Re: Sort view by Last 2 weeks sales difference
            Prasad N
            Prasad N Dec 26, 2018 12:29 PM (in response to Don Wise)

            Hi Don Wise,

             

            your idea Calculating diff is working.

             

            But there is a concern with Latest Week parameter value. When data is updated, a new week added but parameter values is not refreshed to latest data.

             

            How to we set dynamically refresh parameter to latest date from the date filed. ? or select max/latest date from date column/filed ? so that latest week value can be referenced in calculations.

             

             

            Thanks

            • 3. Re: Sort view by Last 2 weeks sales difference
              Don Wise
              Don Wise Dec 26, 2018 2:03 PM (in response to Prasad N)

              Hi Prasad,

              Currently, as far as I know, there's no ability to 'dynamically' update your parameter control to 'Today' or to use the TODAY() function for latest date in the parameter settings. Parameters are static. A workaround would be to Edit your Parameter and set it for a date far into the future so that as new data arrives, it should recognize and display the updated data accordingly.  Hope that helps. 

               

              I think the original question was answered here as well, so please close the thread by marking as correct.  Thx!  Don

              Screen Shot 2018-12-26 at 1.58.56 PM.png

              • 4. Re: Sort view by Last 2 weeks sales difference
                Prasad N
                Prasad N Dec 27, 2018 8:04 AM (in response to Don Wise)

                Hi Don,

                 

                Is there any way instead of opening workbook every week ? I want to automate the updating latest date in workbook, Once workbook is published.

                 

                Thanks,

                PN