5 Replies Latest reply on Dec 7, 2018 1:45 AM by Yuriy Fal

    Need help!!, Calculation with two different dates in a condition.

    Ronnakot Chokkajornwut

      Hi Everyone, I get some problem with a calculated field, the calculation need to use 2 different dates in condition.

      As my example, there are 2 dates for a transaction. (created on, stage create on)

       

      The result from calculation that it should be :

      To display the number of users that created and deposit within selected period.

       

      ***Let me explain more :

      1. If you select filter month is "May" so you would get

      First Bar Chart : number of user that has created on May.

      Second Bar Chart : number of user that has created on May and has deposit stage (stage 3) within May.

      Third Bar Chart : number of user that has created on May and has transfer stage (stage 6) within May.

       

      2. If you select filter month are "Feb-May" so you would get

      First Bar Chart : number of user that has created on Feb until May.

      Second Bar Chart : number of user that has created on Feb - May, and has deposit stage (stage 3) within Feb - May.

      even user that created on Feb and deposit on others month (selected month) would be count in this calculation.

       

      Q_06.png

       

      Q_05.png

       

      So, this is a calculated field that I has created.

      COUNTD(

      IF [Stage No] = '3' //user with stage deposit

      AND YEAR([Created On]) = YEAR([Stage Created On])

      AND MONTH([Created On]) = MONTH([Stage Created On])

      THEN [Opportunity Id]

      ELSE NULL

      END

      )

       

      1. For Single month selected : the result is correct, equal to a result with query from database

      2. But when multiple month are selected : the result is incorrect, when compare with query from database

      (result from calculated field return number of user which has deposit stage in each month, and sum each month to total.)

      (But the way that I hope is to get number of user which has deposit stage within selected period (not in each month and then sum each value of month)

       

      The Correct Answer Should be like sheet : Opt4.2_Overall_by_crossDate (single view should be)

       

      Have you ever got the same or likely this problem, or if you have any solution to solve it.