1 2 Previous Next 22 Replies Latest reply on Nov 23, 2015 3:57 PM by Tina Matt

    Week/Quarter

    Tina Matt

      Hi,

       

      I have attached a sample workbook with some data. The data gets updated every day but with out a day stamp. We just have Week and Quarter.

       

      I am having difficulty getting the Current Partially Completed Week, Last completed week, Quarter To Date fields(QTD). Can you please take a look.

       

      I am looking for a table like the below

       


      2015 Q12015 Q22015 Q32015 Q4
      Last Completed Week
      QTD

       

      and

       

      CustomerQTDPrevious QtrPrevious Year4 Qtr Average
      A
      B
      C
      D

       

      Don Barnetson Jonathan Drummey can you please help get the desired calculations, if you have time.

       

      Thank You!

        • 1. Re: Week/Quarter
          Shawn Wallwork

          Tina you realize that when you ping specific people the rest of us move on to other questions. Because of this I'm not sure you do yourself any favors by pinging people. Maybe I'm wrong.

           

          --Shawn

          • 2. Re: Week/Quarter
            Tina Matt

            Yes, you are correct. I apologize. I will refrain from doing that in the future.

             

            Thanks for pointing it out.

            • 3. Re: Week/Quarter
              puspak Agasti

              Try this:

               

              Pull Quarter and Week in Column

              Measure name in rows

              create a calc field:

              max([Week])=window_max( max([Week]))

              and put it on filter and select true.

               

              Then Create another Cal field for quarterly sales: window_sum(sum([Sales]))


              double click it and then compute using Pane (across)

              1 of 1 people found this helpful
              • 4. Re: Week/Quarter
                Tina Matt

                Thanks puspak Agasti for looking at my question. Your solution works perfectly for Quarter to Date.

                 

                I apologize for not being very clear in what "Last Completed Week" meant.

                When we look at the workbook, we can see that WEEK 8 is the current week in the current Quarter(2015-Q4). I want the sum of sales of each quarter only till the prior week or the last completed week of the current Quarter ie, WEEK 7.

                So sum of sales till the last Completed Week in each Quarter not including the current week (WEEK 8) of the Current Qtr.

                Hope this helps ..

                 

                 

                Sales2014-Q42015-Q12015-Q22015-Q32015-Q4
                Till Last Completed Week
                Including Current Week- Quarter to Date
                Only the Current Week

                 

                I am having a tough time to get all the measure. Can you please help when you have a moment.

                 

                Thanks for your time.

                • 5. Re: Week/Quarter
                  puspak Agasti

                  Can I assume that report will always have latest months data? If so, this one works just fine.. (Assuming the report is running in Nov and the data file contains Nov Month Data)

                   

                  Calculated Fields:

                  calc: window_max(if month(max([Month]))=month(today()) then (max([Week in Qtr])) end)

                  Filter: [calc]=max([Week in Qtr])

                  Previous Week: lookup(sum([Sales]),-1)

                  Sales1: window_sum(sum([Sales]))    (Compute using Week In Qtr)

                  1 of 1 people found this helpful
                  • 6. Re: Week/Quarter
                    Tina Matt

                    Thank you for your time. Appreciate it!

                     

                    Can you please share the workbook itself.

                     

                    Also, I replicated the above solution in my scenario, the Quarter to Date and Current Week numbers work but I am still not able to get the 'Sales till Last completed Week'. I have attached an excel sheet with the desired logic that I want to achieve using tableau.

                     

                    Can you please take a final look at it and let me know how we can get it..

                     

                    Again thanks for your time.

                     

                    Regards,

                    Tina.

                    • 7. Re: Week/Quarter
                      puspak Agasti

                      Can you create another Calculated field:

                      WINDOW_SUM(sum([Sales]),1-INT( right([calc],1) ),-1)

                       

                       

                      Calc is the field I had created earlier, you may have named it differently.

                      What it would do it,

                      Window_sum(Sum(sales,1-8,-1)  <- 8 is 8th week in this case so it would sum up all the values from Week 1 to 7.

                      Let me know if it works.

                       

                      Sorry I don't have permission to attach workbook.

                      1 of 1 people found this helpful
                      • 8. Re: Week/Quarter
                        puspak Agasti

                        • 9. Re: Week/Quarter
                          Tina Matt

                          Puspak,

                           

                          The solution works perfectly. Thank you so much for your time. I replicated in the sample data that I had attached and it is good.

                           

                          But when I replicate it on my real data, the filter that we create is not showing any True or False values but is the only value that is present is NULL. Any idea why it would happen. I have just Quarter and Week in Qtr in the column shelf but and when I drag the filter, it shows always null. But, in the sample workbook it works perfectly..

                           

                          Any idea why it would do that.

                          • 10. Re: Week/Quarter
                            puspak Agasti

                            Can you try computing using

                            Table(across) or Week in Qtr?

                             

                            Pull the filter to filter pan and click ok.

                            Then from the drop down select compute using table (across), let me know if it works.

                            1 of 1 people found this helpful
                            • 11. Re: Week/Quarter
                              Tina Matt

                              I think I figured what is causing the problem.

                               

                              'Month' is a string field but when I convert it to month by changing default properties to date, the month values are becoming null

                               

                              Any idea if there is a work around?

                              • 12. Re: Week/Quarter
                                puspak Agasti

                                which format is it in?

                                can you attach a screenshot?

                                • 14. Re: Week/Quarter
                                  puspak Agasti

                                  Try creating a Calc Field:

                                  DATE([Month]+"-01")

                                   

                                  Thnx

                                  1 of 1 people found this helpful
                                  1 2 Previous Next