8 Replies Latest reply on Jun 24, 2018 4:02 PM by garth.conrad

    datediff calculation

    rajesh.nomula.0

      Hi,

       

      I have scenario here , I have my calculated field like below. But after this calculation my result is getting only 29+ days. I want to show all the value list 0-1, 2-7, 8-14, 15-28, 29 + as columns. even it doesn't have any days falling under that category i want the empty columns to be displayed there. I tried from analysis-> table Layout-> show empty columns , it did not help. I tried commenting each range but it was returning NULL. How can i achieve this, need to show the list in columns.

       

       

      IF     (DATEDIFF('day',[some Date],TODAY())  >=  0 and

             DATEDIFF('day',[some Date],TODAY())  <=  1) THEN "0-1 Days"

      ELSEIF DATEDIFF('day',[some Date],TODAY())  >=  2 and

             DATEDIFF('day',[some Date],TODAY())  <=  7 THEN "2-7 Days"

      ELSEIF DATEDIFF('day',[some Date],TODAY())  >=  8 and

             DATEDIFF('day',[some Date],TODAY())  <=  14 THEN "8-14 Days"

      ELSEIF DATEDIFF('day',[some Date],TODAY())  >=  15 AND

             DATEDIFF('day',[some Date],TODAY())  <=  28 THEN "15-28 Days"

      ELSEIF DATEDIFF('day',[some Date],TODAY())  >=  29 THEN "29+ Days"

      END

        • 1. Re: datediff calculation
          Deepak Rai

          This is doable, but need a workbook

          • 2. Re: datediff calculation
            Jim Dehner

            The first question is do you have data that you expect in each of the buckets?

             

            that is is the calculation working it is just that data that results in nothing with the label you are missing?

             

            Check each clause individually if you need to

             

            If the formula is producing the result consistent with the data then

             

            try this

             

            if that doesn't work please attach your twbx workbook

            Jim

            • 3. Re: datediff calculation
              Mahfooj Khan

              Hi Rajesh,

               

              Try this and let us know if this work. Else kindly share some sample mock up data in packages workbook as suggest earlier by other members.

               

              IF (DATEDIFF('day',[some Date],TODAY()) <=  1 THEN "0-1 Days"

              ELSEIF DATEDIFF('day',[some Date],TODAY()) <=  7 THEN "2-7 Days"

              ELSEIF DATEDIFF('day',[some Date],TODAY()) <=  14 THEN "8-14 Days"

              ELSEIF DATEDIFF('day',[some Date],TODAY()) <=  28 THEN "15-28 Days"

              ELSE "29+ Days"

              END

              • 4. Re: datediff calculation
                garth.conrad

                If the column [some date] has no date then your calculation will not be evaluated. If you put your entire IF statement inside a IFNULL() you can set null values to “0-1 Days”.

                 

                IFNULL( IF...then...elseif...elseif...END, “0-1 Days”)

                 

                G

                • 5. Re: datediff calculation
                  rajesh.nomula.0

                  Deepak RaiMahfooj Khan, jim

                   

                   

                  Thank you for replying. I am attaching workbook. where i have calculated field bucket and bucket list. both have the same calculation as it is not showing the bucket list. I don't have any data for these list, but still i have to show them in the bar chart. My requirement is to show top 5 usernames and remaining usernames as others in the same as stacked bar chart falling under this bucket. Even it has no data it should show on the chart as data will update.

                   

                  Regards,

                  Raj

                  • 6. Re: datediff calculation
                    garth.conrad

                    Raj,

                     

                     

                    I looked at the data you provided. Your 'Bucket' group is calculated versus MAX(Ingestion date) and does not produce values in each of the bucket groupings you listed. So the bar chart you showed is correct.  It will not show 1-7 days because there are no rows of data for that group.

                     

                    Your 'Bucket list' calculation is calculated versus Today() and all the values for each row are over 100 days, thus everything will fall into one bucket.

                     

                    G

                    • 7. Re: datediff calculation
                      rajesh.nomula.0

                      Thanks Garth,

                       

                      But my requirement is  to show that empty columns (bucket list) on the chart. It is not coming on the chart to display.

                       

                      Regards,

                      Raj

                      • 8. Re: datediff calculation
                        garth.conrad

                        Raj,

                         

                        There is another message string asking this same issue, same data. If statement with multiple conditions

                         

                        The issue you have is that your data set has no rows of data that evaluate to “2-7 days”. Your IF statement doesn’t create new row of data, it only adds a value to the rows you have. To get a zero value in the “2-7 Days” group you have to force your data set to add a row that has that value.

                         

                        If you look at the other message string I explained how to do it. It is not elegant but it works.

                         

                        G

                        1 of 1 people found this helpful