7 Replies Latest reply on Aug 13, 2018 10:46 PM by Ray Randall

    Need help with dates

    Jatin Kakani

      Hello folks,

       

      I have two dates - creation date and closed date.

      I want to show closed rate by month where close rate is defined as: cases closed in month but created any time / cases created in month.

      If I show it by closed date, then the case creation count appears incorrect as it only takes cases which were created and closed in that month.

      Could you please help? I've attached a test workbook.

       

      Edit: Close date is named signup_date in the attached workbook.

        • 1. Re: Need help with dates
          Soham Wadekar

          Quick question- what is the name of case closed date ? Signup date or Earliest Signup date?

          • 2. Re: Need help with dates
            Jatin Kakani

            It's signup date.

            • 3. Re: Need help with dates
              Soham Wadekar

              Please find the attachment and let me know if that`s what you are looking for.

              • 4. Re: Need help with dates
                Jatin Kakani

                Thanks for helping out Soham !

                The close rate calculation seems incorrect.

                For example, in the created tab you can see that in Sep 2015, 406 cases were created. Whereas in Closed tab you can see that 19 cases were closed.

                So close rate for Sep 2015 will be 19/406 = 4.67%.

                • 5. Re: Need help with dates
                  Ray Randall

                  Hi Jatin,

                   

                  We can duplicate the data source and blend on the two date fields, Signup Date and Case Creation Date.

                   

                  After blending, we can write a calculation that divides aggregate signups per month by aggregate cases created per month.

                   

                   

                  Hope that helps,

                  Ray

                  1 of 1 people found this helpful
                  • 6. Re: Need help with dates
                    Mahfooj Khan

                    Hi,

                     

                    Find my approach below,

                    You've to self join with the same data source using calculated join conditions. You can create join condition using DATETRUNC() at month to match both Case Create Date and Signup Date.

                    Joining will be more easy approach then blending. Because some of the functions doesn't work properly while data blending. Like COUNTD() / MEDIAN() / LOD{} expressions.

                    then you just need to drag the fields like this and you can get the correct output.

                     

                    Workbook attached for your reference. Let us know if this help.

                     

                    Mahfooj

                    2 of 2 people found this helpful
                    • 7. Re: Need help with dates
                      Ray Randall

                      I agree that a join, instead of a blend, can have certain advantages.  However, in this case, since this is a self-blend we can continue to use the original, unaltered data source for any LOD, median, etc expressions. The only time we need to use the secondary data source is when we are computing the rate since this calculation requires aggregating from both data sources.

                      e.g.  ORIGINAL.Countd(signup case id)/COPY.Count(creation date case id)

                       

                      If you need to use COUNTD(Case ID) then you'll need to use the SELF-JOIN method. But if the provided sample is similar to your actual data, you may be fine with a simple count of IDs.

                       

                      Any other calculations probably won't require the blend.. for example, you could calculate duration using datediff('day', creation date, signup date). The calculation only uses fields in the original data source, so won't be affected by blending limitations.

                       

                      Also, keep in mind, the join has the big disadvantage of multiplying rows due to the relationship between signup date and creation date (cases completed in September can be created in any other month).

                      When using a join, your number of rows grows from 15,302 to 1,847,918. This can impact performance as well as incorrectly aggregate your measures.

                       

                      Try both out and see which works best for you!

                      Here's a video on when to use blending and joining: When to Blend and When to Join

                       

                      Ray

                      2 of 2 people found this helpful