7 Replies Latest reply on Jan 14, 2020 8:01 PM by koushik.neelakantha

    RM  vs Sales in same sheet

    koushik.neelakantha

      Hello everyone, Please find the attached workbook.

       

      I have rolling month sales and  sales field in the same sheet.

       

      My requirement is : Rolling month sales should be irrespective of  year and month filters where as  sales field should get change when we change year and month filter.

       

      Rolling Month sales and direct sales should be in same sheet.

       

      As per the attached workbook, Rolling month sales should be 631 ( Feb 19 to Jan 20) and sales column should get  changed as per filters selection.

       

      Note: we can create a seperate sheet for direct sales field and rolling months, but as per the user requirement i need to keep both the fields in same sheet as i have space constraint and lot of fields.

       

      Regards,

      Koushik.

       

      Mahfooj Khan

      Zhouyi Zhang

      Soumitra Godbole

        • 1. Re: RM  vs Sales in same sheet
          Soumitra Godbole

          Hi Koushik,

           

              I have come up with a solution but am still not perfectly clear what exactly are you trying to do.

           

          Dashboard.png

           

          The trick was to create a new date field by adding 1 month to the current month field

          which has been renamed to Date (to avoid confusion).

           

          Date New

          DATE(DATEADD('month',-1,[Date]))

           

          Rolling Months

          {Fixed [Year New]:Sum(If [Year New]>= 2019 Then [sales] END)}

           

          In the above visual, year = 2019 and month = January gives Rolling Sum =  ____ (Null)

          For Feb 2019 to Dec 2019, Rolling Sum = 631 and for Jan 2020 also Rolling Sum = 631

          Let me now if this looks right.

           

          What sounds weird is that the Rolling Sum of Sales is independent of Year and Month.

          In case of 2019, I understand you will have a total of 631. The question is what are we

          supposed to get when we select 2020 ? I feel that the Running sum should have some

          dependence on the Year.

           

          Please confirm whether this makes sense or not. Also I have attached a twbx file for your

          reference. Best Wishes !


          Sincerely,

           

          Soumitra

          • 2. Re: RM  vs Sales in same sheet
            koushik.neelakantha

            Hello Soumitra Godbole Thanks a for the reply.

             

            Please find the below requirement

             

            For rolling month sales : we are in Jan 2020, rolling month sales should appear from feb 19 till Jan 20. It should automatically change every month.

             

            for example: if we are in Mar 20. rolling month should be from april 19 till mar 20.

             

            Note: even though we change date filters, rolling month sales should be static as it automatically change with rolling months logic (DATEDIFF or DATE ADD)

             

            Sales: This column should change with year and month filters.

             

            NO HARDCODING OF VALUES AND BOTH FIELDS SHOULD BE IN SAME SHEET.

             

            Regards,

            Koushik.

            1 of 1 people found this helpful
            • 3. Re: RM  vs Sales in same sheet
              Soumitra Godbole

              Hi Koushik,

               

                  Thanks for the explanation. Here is my modified solution:

              Dashboard.png

              Calculated Fields:

              It required the following 3 calculated fields (using LOD and Today() function)

               

              Formula 1.png

              Formula 2.png

              Formula 3.png

               

              Building the Visual:

              Simply place the above fields in their respective shelves as shown below

               

              Visual.png

               

              Hope this explanation was useful and made sense. Please note that the Rolling Months is fixed

              within a given period (in this case from Feb-19 to Jan-20) so as long as the year month selection

              is within this range it should work fine.

               

              If we go out of range for eg:- we select Mar-2020, when we are currently in Jan-2020, the Rolling

              Months will be blank. This is based on my understanding of your explanation.

               

              To see future dates, instead of using the Today() function, you will need to create a Parameter for

              [Year] and Parameter for [Month]. For your reference, I have attached the twbx file. Let me know if

              you have any questions or need any modification. Thanks !


              Sincerely,

               

              Soumitra

              • 4. Re: RM  vs Sales in same sheet
                Soumitra Godbole

                Hello again,

                 

                    Not so fast ! I challenge my earlier solution as it looks a bit retarded. Here is a

                really cool solution that is way more simpler than the earlier solution. Let me know

                your thoughts.

                 

                New  1.png

                Here we directly use values from your filters (Month & Year) to change the values of the Rolling Month

                and Monthly Sales (instead of using Today() as in the earlier solution). Also we do not even need to use

                any Parameters.

                 

                New  2.png

                That's all folks. Also in addition to your dashboard, I wanted to visualize how the Monthly

                Sales would vary in comparison to the Rolling Months Sales which would be similar to

                the Greatest Integer Function [  ] and appear as a Staircase or Step chart.

                 

                Anyway, I have attached the twbx file for the above solution and hope this  was cool and helpful.

                Best Wishes !


                Sincerely,

                 

                Soumitra

                1 of 1 people found this helpful
                • 5. Re: RM  vs Sales in same sheet
                  koushik.neelakantha

                  Hi soumitra, Thanks a lot for the reply and sharing workbook.

                   

                  Please find the attached work book and excel file.

                   

                  I have used below logic for Rolling months so that when i try to change year and month filters, Rolling months is static and sales YTD is getting changed.

                   

                  {fixed :SUM(if DATEDIFF('month',[month],TODAY())<12 and DATEDIFF('month',[month],TODAY())>=0 then

                  ([sales])

                  END)}

                   

                  My query is when we enter Feb month, will my Rolling months give me 620 automatically ( Feb 20 to march 19) ??

                   

                  Expected output should be :

                   

                  1) even though when we select month and year filters ,Rolling months column should be static. It should change when we enter new month automatically.

                   

                  Example: when we enter next month ie, Feb 2020, rolling months field should show 620 automatically.

                   

                  2) Sales YTD should change with month and year filters.

                   

                  3) Rolling months and Sales YTD should be present in same sheet

                   

                  Regards,

                  Koushik.

                  • 6. Re: RM  vs Sales in same sheet
                    Soumitra Godbole

                    Hi Koushik,

                     

                        You just lost me with your last example using Feb 20 to Mar 19. Shouldn't it

                    be Mar 19  to Feb 20 as per your earlier explanation. Your method shown makes

                    sense but it will give you the sum total for 2019 & 2020. What we need is to have

                    a field that separates the 12 month periods (Feb to Jan) ie

                     

                    Jan19,

                    Feb 19 to Jan 20,

                    Feb 20 to Dec 20.

                     

                    That was the tricky part and so I came up with the idea of creating a field Date which

                    would be 1 month behind the month field. This way Feb 19 to Jan 20  in the Month field

                    would mean Jan 19  to Dec 19 in the Date field. This also simplifies the LOD calculation.

                     

                    New++2.png

                    What my 2nd equation does is it simplifies the whole calculation. Here is what it would

                    appear if done in Excel. I have also shown the Date (-1 month) field below to give you

                    an idea

                     

                    Test.png

                     

                    I did confirm I am getting the above numbers in both my Tableau solutions.

                    The only difference being is that in my first solution, I have used Today

                    which means until we reach 1st Feb 2020, the Rolling Month value will

                    be 631 and on 1st Feb, it will change to 716.

                     

                    Please let me know if this makes sense and solves your problem.


                    Sincerely,

                     

                    Soumitra

                    1 of 1 people found this helpful
                    • 7. Re: RM  vs Sales in same sheet
                      koushik.neelakantha

                      Hi soumitra,

                       

                      Using LOD solved the issue thanks a lot for providing the logic.

                       

                      will implement the same in real time and check.

                       

                      Regards,

                      Koushik.