1 2 Previous Next 23 Replies Latest reply on Nov 12, 2018 4:58 PM by Dan Cory

    Missing a month in view

    A J

      HI ALL,

       

      I used following calculation and somehow cant figure out WHY am I getting a missed month (7 in example shown) in view ????

       

      and HOW can we get 1st of every month dates ONLY in filter, instead of all days , using this calculation or any suggested, as I have month and year number in data source as integers???

       

      calculation : DATE(STR([Month]) + " 1 " + STR([Year]))

       

      img:

      Untitled.png

        • 1. Re: Missing a month in view
          Naveen B

          Hi A J,

           

          Could you please attach a sample workbook

           

          BR,

          NB

          • 2. Re: Missing a month in view
            Cédric Tran

            Hi,

             

            You don't see July because you filtered by field called "Start of Month" and you selected July 25th which is not the first day of july.

             

            To create your own date based on the numerical value of month and year of your datasource, you can use MAKEDATE function.

             

             

            Cedric

            • 3. Re: Missing a month in view
              A J

              Thanks for your reply Cedric,

               

              BUT I receive following error on using MakeDate() function in my tableau 10.5 ???

               

              Thanks,

               

              Untitled.png

              • 4. Re: Missing a month in view
                A J

                What I need is a mm/yyyy filter which SHOULD contain 1st of every month DAY only and must not include rest of the dates in filter

                 

                any suggestions ?

                 

                Thanks

                • 5. Re: Missing a month in view
                  Ritesh Bisht

                  Here you go,

                   

                  Screenshot 2018-11-10 at 7.21.56 PM.png

                   

                  Just filter the 1st day of any month with below

                   

                  { FIXED month([Order Date]) : min([Order Date])}

                   

                   

                   

                  If you want to fix the total as well to 1st of every month then use below otherwise leave it

                   

                  { FIXED  { FIXED month([Order Date]) : min([Order Date])}

                   

                   

                  : SUM(IF { FIXED month([Order Date]) : min([Order Date])}

                   

                   

                  =[Order Date] then [Profit] end) }

                   

                   

                  Thanks,

                  Ritesh

                  Please mark the answer as CORRECT & HELPFUL if it really helps you

                  • 6. Re: Missing a month in view
                    Cédric Tran

                    Hi

                     

                    MAKEDATE( ) with 3 parameters of type integer separated by comma like this: MAKEDATE( 2018, 1,1).

                     

                    It exists in Tableau 10 the same way,

                     

                    Cedric

                    • 7. Re: Missing a month in view
                      A J

                      Cedric,

                       

                      I tried your way as well!!!!!  :-(

                      error statement :"Unknown function MakeDate() is called"

                      Untitled.png

                      • 8. Re: Missing a month in view
                        A J

                        Thanks for the reply Ritesh,

                         

                        I need a continuous Date Slider Quick Filter. Can you make a continuous Date filter with 1st of every month and share the workbook.

                         

                         

                        I appreciate your LOOK into this scenario

                         

                        Regards,

                        • 9. Re: Missing a month in view
                          A J

                          Ritesh,

                           

                          I'm not using a genuine date like 'order date' as you mentioned in your solution. I have MONTH and YEAR as integers/numbers in my data source and having aggregated data against those.

                           

                          Thanks,

                          • 10. Re: Missing a month in view
                            Ritesh Bisht

                            Many functions like this(MAKEDATE) are dependent on the data source. If there is no equivalent function or cannot be translated to an equivalent function in that data source, the function will not be available.

                             

                            The primary workaround for this is to use an extract.

                             

                            Thanks,

                            Ritesh

                            • 11. Re: Missing a month in view
                              Ritesh Bisht

                              Try this

                               

                              Screenshot 2018-11-10 at 11.46.33 PM.png

                               

                               

                              Copy paste here ---> DATE( STR(YEAR([Order Date]))+'-'+STR(MONTH([Order Date]))+'-'+'1')

                               

                              You don't need my FIXED formulae , only above step is required I guess

                               

                              Please check

                               

                              Thanks,

                              Ritesh

                              • 12. Re: Missing a month in view
                                A J

                                I already have a data source as LIVE, published on server and its shown as LIVE in type on server but I also have an extract of it which is scheduled. I dont know whether I can et the makedate() function on such type of an extract which is LIVE shown on server ????

                                what's the diff btwn an extract and LIVE on T server ??

                                 

                                Thanks,

                                • 13. Re: Missing a month in view
                                  A J

                                  Thanks again for keeping on with your solution Ritesh.

                                   

                                  Before pasting my question here I tried out with this calculation: DATE("01" + " " + str([Month]) + " " + str([Year])) and worked perfectly for me even with the continuous filter as well BUT my client needs mm/dd/yyy format and when I change its date format for continuous date filter it messes up and starts skipping month and only shows perfect view in pane for 1st of the months which makes sense as far as T is concerned BUt not with my view and as/requirements. requirment is to always have a date slider and I have MONTH n YEAR as numbers so HOW can I make a custom date slider continuous date filter with this ????

                                   

                                  Im attaching the result I get against calculations that you provided latest. it skips the month as you can see and in img2 it will show correct view as Im selecting 1st of the month in continuous date filter slider:

                                   

                                  Untitled.png

                                   

                                  img2:

                                  Untitled.png

                                   

                                  Thanks for looking into this mate!

                                  • 14. Re: Missing a month in view
                                    A J

                                    I also tried SHAWN's method, mentioned in last of the thread : https://community.tableau.com/message/226308?et=watches.email.thread#226308

                                    AND I'M ALSO FACING THE INTERESTING BEHAVIOR LIKE MENTIONED IN THE THREAD AT LAST:

                                    "Weird thing is that the only code that is close is DATE(STR(  )+'.1.'+STR() ).

                                     

                                    That however mixes up months and dates"

                                     

                                    any suggestions plzz ???

                                    1 2 Previous Next