13 Replies Latest reply on Aug 5, 2016 2:03 PM by Vishal D

    # of days in Month & Year

    Vishal D

      I have [Report Year] = [2016,2015,2014,2013,2012] & [Report Month] = [1,2,3,4,5,6,7,8,9,10,11,12] as my fields in database.

       

      How will I calculate below items.

       

      1. # of days in Month for the filtered/selected [Report Month] and [Report Year]

      2. # of days in Year for the filtered/selected [Report Year]

        • 1. Re: # of days in Month & Year
          Vishal D

          User Option 1 - I have to make the dashboard dynamic based on the current month when the user runs it and use that month for calculation.So if user runs the dashboard today then I have to calculation '# of days for Aug 2016'

           

          User Option 2 - [Report Year] = [2016,2015,2014,2013,2012] & [Report Month] = [1,2,3,4,5,6,7,8,9,10,11,12] will be filter options for end user to select defaulting to current year and month. But user has flexibility to select any other month/year so I want to calculate # of days based on what is selected by user.

          • 2. Re: # of days in Month & Year
            Tom W

            As per your other thread, you need to upload a Tableau Packaged Workbook including some sample data. Your data as you've described it is not clear. Is it one field concatenated or are you describing values within a dimension for example? If you upload a packaged workbook showing what you've managed to do, it will be much easier.

            • 3. Re: # of days in Month & Year
              Vishal D

              attached is sample data.

               

              Does this help?

              • 4. Re: # of days in Month & Year
                Tom W

                It would be better if you created a Tableau Packaged Workbook showing what you've managed / tried so far.

                 

                1. # of days in Month for the filtered/selected [Report Month] and [Report Year]

                First you need to convert this to an actual date, the first date of the month.

                CalcDate = dateparse('yyyy-MM-dd',str([Report Year]) + '-' + str([Report Month]) + '-01')

                Then you can calculate the number days as follows - datediff('day',[CalcDate],dateadd('month',1,[CalcDate]))

                 

                2. # of days in Year for the filtered/selected [Report Year]

                Use a similar approach for the year. Figure out the first day of the year using [Reporting Year] + '-01-01' and the last day as [Reporting Year] + '12-31' and use the date diff function as above to return the number of days.

                • 5. Re: # of days in Month & Year
                  Vishal D

                  Hi Tom,

                   

                  1. I dont see function called dateparse(). Its throwing error in my calculated field.

                  I am using 9.2 version

                   

                  2. I dont think I can hardcode '12-31' all the time as if its current year then the # of days should be as of today.

                   

                  Appreciate your help

                  • 6. Re: # of days in Month & Year
                    Tom W

                    Please check if the datasource you're using supports dateparse - Understanding the DATEPARSE Function | Tableau Software

                    You could just use the date conversion function i.e. DATE([Year] + "-1-1")

                     

                    2. Then just pass NOW() into the date diff function - datediff('day', [First Date], Now())

                    If it needs to be dynamic based off the filter, create an if statement for the last date parmeter in the date diff function like;

                     

                    datediff('day',[First Date],if year(Now()) = [Year] then Now() else date([Year] + "-12-31"))

                    • 7. Re: # of days in Month & Year
                      Vishal D

                      Tom, I am not clearly understanding your suggestion.

                       

                      Can you please be little more specific and would appreaciate if you can help with specific formulas.

                       

                      To keep it simple I just want to calculate # of days of month & year selected in the user filter and display it.

                       

                      My datasource is HANA View

                       

                      I have [Report Year] = [2016,2015,2014,2013,2012] & [Report Month] = [1,2,3,4,5,6,7,8,9,10,11,12] as my fields in database.

                      • 8. Re: # of days in Month & Year
                        Tom W

                        To calculate the number of days in a month when you are using a combination of a [Report Year] (i.e. 2016) and [Report Month] i.e. 9.

                         

                        datediff('day',date(str([Report Year]) + '-' + str([Report Month]) + '-01'),dateadd('month',1,date(str([Report Year]) + '-' + str([Report Month]) + '-01')))


                        • 9. Re: # of days in Month & Year
                          Vishal D

                          You are awesome Tom. How about # of days in Year?

                          • 10. Re: # of days in Month & Year
                            Tom W

                            I'm not going to give you the straight up answer here, because you won't learn what you're actually doing and what you're asking for it's that different to the month calculation above. if you understand how it works, you'll be able to apply the tweaks your self.

                             

                            datediff('day',StartDate,EndDate)

                             

                            Breaking down the datediff calculation above.

                            • In red you specify which unit of measurement you want to use to calculate the difference in the dates i.e. hours or months
                            • In blue, you pass in a start date
                            • In green, your end date which you will subtract the start date from.

                             

                            You're asking "How about # of days in year" > you really need to be more specific. Do you just generally mean how many days in a year? Or do you want to run it for a specific year?

                            I think what you're asking is "How many days have elapsed in a year based on the Report Year field".

                             

                            Assuming that is correct, the StartDate parameter to the date diff would be; date(str([Report Year]) + '-01-01') as you want to default it to January.

                            The EndDate is trickier. If it's last year, you could just default it to December 31st. If it's this year though, I think you want days elapsed and thus, you would need to use the current date.

                             

                            So for your END date, you'll need to write an IF statement which determines if the [Report Year] is the current year. If it is the current year, then you would return the Now() function otherwise, you would return December 31st for that year.

                            The format as an example should be: IF something THEN date ELSE anotherdate END

                             

                            See how far you get and let me know where you get stuck.

                             

                            More help topics;

                            Logical Functions (If statements)

                            Date Functions (NOW function)

                            • 11. Re: # of days in Month & Year
                              Vishal D

                              thanks tom for helping the right way

                               

                              getting close but getting error

                               

                              DATEDIFF('day',date(STR([Report Year])+'01-01'),

                              IF ([Report Year]=DATEPART('year',NOW()))THEN NOW()

                              ELSE date(STR([Report Year])+'12-31')END)

                               

                               

                               

                              [SAP AG][LIBODBCHDB DLL][HDBODBC] General error;303 invalid DATE, TIME or TIMESTAMP value: Error in column Calculation_541839374006018050: attribute value is not a date or wrong syntax;int comma(longdate '2016-08-04 16:11:22.1590000', longdate '2016-08-04 16:11:22.1590000', string string(int "COL$0$"), int days_between(longdate longdate(daydate [here]daydate(string sqladd(string "__common1", string '01-01'))), longdate box(int "COL$0$", int component(longdate longdate(longdate "$$place_holder_0$$"), int '1'), longdate longdate(longdate "$$place_holder_1$$"),

                              • 12. Re: # of days in Month & Year
                                Vishal D

                                Tom, I am working on attaching twbx with dummy data for better explaining my issues.

                                 

                                I tried building below formula to calculate # of days in a year. For prior year I should consider full year but for current year I have to calculate days as of today.

                                 

                                Can you please advise what I am missing?

                                 

                                DATEDIFF('day',date(STR([Report Year])+'01-01'),

                                IF ([Report Year]=DATEPART('year',NOW()))THEN NOW()

                                ELSE date(STR([Report Year])+'12-31')END)

                                • 13. Re: # of days in Month & Year
                                  Vishal D

                                  Fixed it, was missing '-' before the dates.

                                   

                                  Thanks a lot Tom

                                   

                                  DATEDIFF('day',date(STR([Report Year])+'-01-01'),

                                  IF ([Report Year]=DATEPART('year',NOW()))THEN NOW()

                                  ELSE date(STR([Report Year])+'-12-31')END)