1 2 Previous Next 22 Replies Latest reply on Oct 5, 2016 11:01 AM by joe major

    Need Current month, Quarter to date, and Year to Date and now: Quarter over Quarter $ and Q over Q % all on one sheet

    joe major

      Hi,

       

      UPDATE:  thank you Tom and everyone for your help.  Everything is working fine.  I did have two new requirements added today and need some help. 

       

      I need:

       

      1) Quarter over Quarter (dollars)

      and

      2) Quarter over Quarter (%)

       

      I can do these of course on separate sheets, but again,  with the custom calcs I'm not sure how to pull it off.  Again, they want them on the same sheet.  SEE SL1 sheet for what I've already done.

       

      Thank you!

       

      ____________________________________________________________________________________________________________________________________________________

       

       

       

      I'm trying to have 3 calculations on one sheet but am having trouble.

       

      1) I need to show the current month of sales in one column.  Out of all the months in a year, show the current one (based on date?)

      2) I need to show Quarter to date in the next column.

      3) and then Year to Date in the next column.

       

      I'm not sure how to do all three with out the filters affecting each column.

       

      I've attached a workbook with sales store data.  I can't send the real data because it's proprietary....I'll have to take your help and plug in my date and $ fields.

       

      Any help is appreciated.

       

      Joe

        • 1. Re: Need Current month, Quarter to date, and Year to Date all on one sheet
          Matt Hong

          Joe,

           

          I cannot access your workbook, but I can tell you that your easiest solution will be to just create three different sheets and piece them together on a dashboard.

           

          Please let me know if there are reasons you cannot do this.

          • 2. Re: Need Current month, Quarter to date, and Year to Date all on one sheet
            Tom W

            Hi Joe,

            Further to Matt's comment, you need to extract your datasource before saving as a packaged workbook.

             

            With that being said, I don't think I'll need it. Take a look at the attachment;

            I have created three calculated fields, one for each measure you need.

            Note that each of the fields uses the datetrunc function to create the 'filter' on the period. I'm referencing a field called PretendDate which basically returns today in 2015 as the superstore sample dataset doesn't have 2016 data.

            • 3. Re: Need Current month, Quarter to date, and Year to Date all on one sheet
              joe major

              Hi Tom,  I'm unable to open your workbook.  I'm using 9.3.  Attached is a new workbook, extracted and saved as a packaged workbook.

              • 4. Re: Need Current month, Quarter to date, and Year to Date all on one sheet
                joe major

                Thanks Matt.  Unfortunately, I need them all on one.  Thanks for trying.

                • 5. Re: Need Current month, Quarter to date, and Year to Date all on one sheet
                  Tom W

                  Hi Joe,

                  Use this as your basis:

                   

                  SUM(if datetrunc('month',[Order Date]) = datetrunc('month',[Now()]) then [Sales] end)

                   

                  This will get you the current month of sales.

                  Change the datetrunc parameter to quarter for quarter to date and year for year to date.

                  2 of 2 people found this helpful
                  • 6. Re: Need Current month, Quarter to date, and Year to Date all on one sheet
                    joe major

                    I'll give it a try.  Thank you.

                    • 7. Re: Need Current month, Quarter to date, and Year to Date all on one sheet
                      joe major

                      Hi tom,

                       

                      I see

                       

                      SUM(if datetrunc('month',[Order Date]) = datetrunc('month',[Now()]) then [Sales] end)

                       

                      but it says Now() is unknown.

                       

                      I'm uploading a cross tab of how I had to make dates .  I have to start out with Fiscal Period that has it's date set up like: 201601, 201602, etc.  I have several calculations that break these apart.  My results are in the cross tab.  Maybe that'll help.

                      months.JPG

                      • 8. Re: Need Current month, Quarter to date, and Year to Date all on one sheet
                        Tom W

                        Try using TODAY() instead of NOW()

                        • 9. Re: Need Current month, Quarter to date, and Year to Date all on one sheet
                          joe major

                          Trying to plug in my dimensions, and it looks like:

                           

                          SUM(if datetrunc('month',[Fisc Month]) = datetrunc('month',TODAY()) then [CA Service Bookings Net] end)

                           

                          Fisc Month is:

                          'FY' + MID([Fiscal Period ID],3,2) + '-' + [Digits Fisc Month] + '-' + [Cal String Fisc Month]

                          and

                          CA Services Bookings Net is the dollars.

                           

                          months.JPG

                           

                          I tried to make it so Tableau could recognize my dates using some calculated fields based on the Fiscal Period ID.

                           

                           

                          But am getting a error.  datetrunc is being called with a (string, string), did you mean (string, datetime)

                           

                          Thanks for all of your help....sorry to bother again.

                          • 10. Re: Need Current month, Quarter to date, and Year to Date all on one sheet
                            Tom W

                            The problem is you don't have a 'real' date in that dataset, so you have two options;

                            1. Force a 'real' date out of that data so you can use date trunc

                            2. Create calculations which can be applied to your fiscal periods. This would be a little more work, but we could go down that path if approach one didn't work.

                             

                            I think the 'right' approach will largely depend on how custom things get with your quarters and fiscal years etc. I'm guessing that "August 1st 2016" is 201701?

                            If so, I'd just create a date which defaults to the first day of the month as follows:

                            DATEPARSE("yyyyMM",str([Fiscal Period ID]))

                             

                            However, this will interpret FY17-01-Aug as January 1 2017. So, use the dateadd function to deduct 5 months;

                            DATEADD('month',-5,DATEPARSE("yyyyMM",str([Fiscal Period ID])))

                             

                            Now you have a date which aligns your Fiscal Period ID to the actual calendar. Right click your new calculated field, select 'Default Properties', 'Fiscal Year Start' and change it to August.

                            Then if you go back to my earlier date trunc calculations, you should be able to implement those fine.

                             

                            If not, please upload a Tableau Packaged Workbook including sample data which matches your real data.

                            • 11. Re: Need Current month, Quarter to date, and Year to Date all on one sheet
                              joe major

                              3) Hi Tom,

                               

                              I've been able to set up the month date trunc, and it works fine.   But I don't know how to make it show only the present month.  also, two more issues is that I need 2 more calcs. next to each other in the view,

                               

                               

                              Column 1 will be current month.  Column 2 will be QTR to date and column 3 will be Year to Date.

                              sls.JPG

                              so I the end, I need 3 calcs .

                               

                              1) Current Month

                              DATEADD('month',-5,DATEPARSE("yyyyMM",str([Fiscal Period ID])))

                              2) QTR to Date

                              DATEADD('month',-5,DATEPARSE("yyyyMM",str([Fiscal Period ID])))

                              3) Year to Date

                              DATEADD('month',-5,DATEPARSE("yyyyMM",str([Fiscal Period ID])))

                               

                               

                              Or could we do something like this for each calc:

                               

                              Current Month

                               

                              if

                              ([Fiscal Period ID] = "201701" then [CA Bookings Net]

                              else null end

                               

                              Quarter to Date

                              if

                              ([Fiscal Period ID] = "201701"  and "201702" then [CA Bookings Net]

                              else null end

                               

                              Year to date

                               

                              ([Fiscal Period ID] = "201701"  and "201702" then [CA Bookings Net]

                              else null end

                              Adding in months as necessary.

                               

                              If you need the workbook, let me know.  I'll try to make it generic.

                              • 12. Re: Need Current month, Quarter to date, and Year to Date all on one sheet
                                Tom W

                                Joe,

                                You need to follow my steps in my previous post to create the fake date. I.e. a calculated field called FakeDate = DATEADD('month',-5,DATEPARSE("yyyyMM",str([Fiscal Period ID])))

                                 

                                Then you need to use the calculations from my original post to get the sales numbers for that month, qtr and year.

                                I.e.

                                ThisMonth = SUM(if datetrunc('month',[FakeDate]) = datetrunc('month',[TODAY()]) then [Sales] end)

                                ThisQTR = SUM(if datetrunc('quarter',[FakeDate]) = datetrunc('month',[TODAY()]) then [Sales] end)

                                ThisFinancialYear = SUM(if datetrunc('year',[FakeDate]) = datetrunc('month',[TODAY()]) then [Sales] end)

                                 

                                If you're still struggling then yes, I will need a packaged workbook to help you go further

                                • 13. Re: Need Current month, Quarter to date, and Year to Date all on one sheet
                                  joe major

                                  Thanks Tom,

                                   

                                  Making more sense now.

                                   

                                  So I need to make  3 fake dates to correspond with each new column..  I thank you for your patience .  I’ve just never done this before.  What would be the settings for the last two fake dates?

                                   

                                  I suppose ‘month’ should be replaced with ‘quarter’ and’ year’ respectively.  What about the -5  …does that change?  Does anything else change?

                                   

                                   

                                   

                                  ThisMonth = SUM(if datetrunc('month',[FakeDate1]) = datetrunc('month',[TODAY()]) then  end)

                                   

                                  FakeDate1 = DATEADD('month',-5,DATEPARSE("yyyyMM",str()))

                                   

                                   

                                   

                                  ThisQTR = SUM(if datetrunc('quarter',[FakeDate2]) = datetrunc('month',[TODAY()]) then  end)

                                   

                                  FakeDate2 = DATEADD('month',-5,DATEPARSE("yyyyMM",str()))

                                   

                                   

                                   

                                  ThisFinancialYear = SUM(if datetrunc('year',[FakeDate3]) = datetrunc('month',[TODAY()]) then  end)

                                   

                                  FakeDate3 = DATEADD('month',-5,DATEPARSE("yyyyMM",str()))

                                  • 14. Re: Need Current month, Quarter to date, and Year to Date all on one sheet
                                    Tom W

                                    Joe, You need one fake date, three calculated fields as above. They all reference the same fake date, the datetrunc takes care of the filtering / controlling which month/quarter/year it falls into.

                                    1 2 Previous Next