12 Replies Latest reply on Feb 22, 2018 4:34 AM by rakhesh.nellikoppa

    Quarter Sales Question  - Need community help

    rakhesh.nellikoppa

      Hi Community@@@@@@@,

       

      I am looking quarter level sales but based on some logic : if I select one date then  --- selected date Sales + Prev month Latest Date + Prior to Prev Month Latest Date Sales after that need to aggregate at quarter level. If my current month is Feb then Selected date Sales + Prev month Latest Date Sales (Jan) at Product Level.

      For Dec - selected Prod Sales + Prev month Latest date  + Prior to Prev Latest date sales then aggregate at quarter level.

       

      Please check the expected result column, that's my expected result

       

       

      I have tried to do this using LOD but didn't achieved the expected result, attached the sample workbook for your reference, please look into this.

       

      Thank you.

       

      Regards,

      Rakesh

                                                                                                                                                                             

      DateQuarterProductSalesExpected Result
      09-02-2018Q1A513
      01-02-2018Q1A1018
      15-01-2018Q1A88
      10-01-2018Q1A22
      12-12-2017Q4A1515
      11-12-2017Q4A2060
      11-11-2017Q4A1540
      11-10-2017Q4A2525
      09-02-2018Q1B2332
      01-02-2018Q1B1019
      18-02-2018Q1B514
      10-01-2018Q1B99
      12-12-2017Q4B1549
      31-12-2017Q4B2054
      21-11-2017Q4B1934
      28-10-2017Q4B1515
        • 1. Re: Quarter Sales Question  - Need community help
          rakhesh.nellikoppa

          Hi,

           

          Please let me know if anyone need any more information/clarification.

          Pooja GandhiSimon RuncJoshua Milligan

          Thank you.

           

          Regards,

          Rakesh

          • 2. Re: Quarter Sales Question  - Need community help
            Simon Runc

            hi Rakhesh,

             

            So can you give me a couple of examples of the expected figure, and where it comes from

             

            If I look at Product A

             

             

            It looks like the Expected Result for the 11/12/2017 is Current Day's Sales (11/12/107 = 20) + Previous Months Sales (11/11/2017 = 15) + Month Before That (11/10/2017 = 25) = 60. But why does the expected value for 12/12/2017 = 15?

             

            And can't work out why the result for 09/02/2018 = 13

             

            If you can detail out the examples for December and February (with the 2 or 3 values which make up the Expected)...similar to how I have above. I'll take a look

            • 3. Re: Quarter Sales Question  - Need community help
              rakhesh.nellikoppa

              Hi Simon Runc,

              Thanks for looking into this, Yes you are right for 11th Dec 2017 expected figure is 60, I corrected now and shown with excel formula as below.

              Formula for  :

              Current Day Sales + Prev Month Latest Day Sales + Month Before that Latest day sales .

               

              Thanks again checking and let me know if you need any more information.

               

              Regards,

              Rakesh

              • 4. Re: Quarter Sales Question  - Need community help
                Simon Runc

                Thanks for that...So just to double check I've understood correctly

                 

                It's the Current Days Sale + The Last DAY of the Previous Month (and so not the entire last month) + The Last DAY from the Month before (again, not the entire month)?

                 

                And you want to be able to select a single date, and then have that information shown for the selected Month? If so, I don't think we'll be able to use LoDs as we need to move back through the months dynamically (eg. if Feb is selected we need to get data from Jan and Dec, but if March is selected we need to get data from Feb and March). The easiest way would be to use a parameter to select the date, can you let me know if this is an option? If not, then we are (probably) going to have to use a Table Calculation Filter, in which case we can't apply this across worksheets, so can you let me know if this will be a stand-alone sheet, or if you plan to use in a multi-sheet dashboard, where the selection of the date would need to reflected in multiple sheets.

                 

                Thanks

                • 5. Re: Quarter Sales Question  - Need community help
                  rakhesh.nellikoppa

                  Hi Simon Runc

                   

                  It's the Current Days Sale + The Last DAY of the Previous Month (and so not the entire last month) + The Last DAY from the Month before  ( at Product Level and Quarter Level Aggregation )

                   

                   

                  As of now we can show the result as cross tab with all the dates, we don't select any dates and will show all the dates information to make it simple. Use your easy method (LOD) and write the logic.

                   

                  Ex :  ((Selected mean particular row ))

                  For March we should count Selected Date + Feb Latest Day Sales + Jan Latest Day Sales

                  For Jan we should count only Jan month selected Sales

                  For Nov We should count Selected date sales + Oct latest day sales

                   

                  let me know if any confusion/or i am making it complex to explain.

                   

                  Thanks again.

                   

                  Regards,

                  Rakesh

                  • 6. Re: Quarter Sales Question  - Need community help
                    Simon Runc

                    So I don't think there is an LoD solution, as we need to move up through the Partitions (Months), and so need self-referencing calculations...ie. Table Calculations.

                     

                     

                    This is pretty tricky to do, as Tableau don't think like Excel (cell based Excel vs Set Based Tableau and Databases) so can be tricky to move up and down "cells". I think I've found  a way, so if you can check a few of the "Expected Values" and if all is working as you expect, I'll try and explain how it works!

                    1 of 1 people found this helpful
                    • 7. Re: Quarter Sales Question  - Need community help
                      rakhesh.nellikoppa

                      Thanks Simon Runc

                       

                      I will check and update you. you are always awesome and helpful.

                       

                      Thanks again.

                       

                      Regards,

                      Rakhesh

                      • 8. Re: Quarter Sales Question  - Need community help
                        rakhesh.nellikoppa

                        Thanks a lot Simon Runc.

                         

                        Your logic works superbly, but need sometime to understand the behavior as its more on Table calculation. I am doing reverse engineering and slowly understanding, will get back to you if required any more clarification.

                         

                        As of now i am good and thanks again for your timely help.

                         

                        Regards,

                        Rakesh

                        • 9. Re: Quarter Sales Question  - Need community help
                          Simon Runc

                          Cool..glad it did the trick!

                           

                          So yes it's pretty complicated!

                           

                          The LoD [Last Date of Each Month] just gets the last entry of each month, for each product

                          and then [Sales Last Day of Month] uses this to only populate the Sales for that row.

                           

                          [Sales Previous Month]

                          LOOKUP(SUM([Sales Last Day of Month]),-1)

                           

                          Gets the sales of the last day of the month from the month before, with this set up (and in this order so month above date...you can drag and drop to re-order)

                           

                          However where there are 2 entries in a month, the second one (being more than 1 partition away) doesn't get the sales (of last day) of previous month, so we use this formula to populate that row

                          [Sales Previous Month - Populated]

                          IF ISNULL([Sales Previous Month]) THEN PREVIOUS_VALUE([Sales Previous Month])

                          ELSE [Sales Previous Month]

                          END

                           

                          and is set up like this...Notice the restarting every month

                           

                          and then the month before is the same, but the lookup is -2

                           

                          Then we can just add them all together...hope that helps get you started on the reverse engineering

                          1 of 1 people found this helpful
                          • 10. Re: Quarter Sales Question  - Need community help
                            rakhesh.nellikoppa

                            Hi Simon Runc,

                             

                            Thanks for the detailed explanation.

                             

                            This is working fine with small data set, but when i changed to more data. Its looking weird.

                            For Oct and Nov month (First 2 months of quarter) its working fine however its not working as expected for the last month of the quarter where we add prev + month before. Prev month value is fine but Month before its not taking properly. in the attached example for Dec month its not working as expected and its taking month before only for last day of Dec.

                             

                            Attachment Name : Specific Previous Rules_issue_v1 (Attached check my post top )

                             

                            Am i  missing something ?

                             

                            Please take a look.

                             

                            Thank you

                             

                            - Rakesh

                            • 11. Re: Quarter Sales Question  - Need community help
                              Simon Runc

                              hi Rakhesh,

                               

                              Yes I think I know the problem...The previous solution happened to work with that dataset, but hadn't foreseen how it would behave with different data (it can be like these with these Excel type formulas, there can be a bit of playing to ensure it works for all situations)

                               

                              The problem was that, depending, on the number of data-days in each month, it wasn't always going back and getting the Month Before properly, which only existed in a single row (the last day of the month), so I've made the following change

                               

                              I've added this formula, to populate the last day of the month figure to every row that month

                              [Sales Last Day of Month - LoD to Every Day]

                              {FIXED [Product], DATETRUNC('month',[Date]): SUM(IIF([Last Date of Each Month],[Sales],NULL))}

                               

                              and then used this field in stead of [Sales Last Day of Month] in the other formulas

                              such as

                              [Sales Previous Month]

                              LOOKUP(SUM([Sales Last Day of Month - LoD to Every Day]),-1)

                               

                              Hopefully that does the trick!

                              1 of 1 people found this helpful
                              • 12. Re: Quarter Sales Question  - Need community help
                                rakhesh.nellikoppa

                                Thanks a lot Simon Runc.

                                 

                                This is working exactly the way i was looking, and thanks for quick turnaround.

                                 

                                You are amazing and so helpful.

                                 

                                Thanks again and have a good day.

                                 

                                Regards,

                                Rakesh