1 Reply Latest reply on Feb 11, 2018 2:22 PM by Yuriy Fal

    Calculating field to get distinct count of account ID filter on 2 date within a date range

    Clayton Farrugia

      HI

       

      I am facing an issue regarding how to use calculated field using date range filter. Basically, I have the following scenario:

      I am trying to count active depositors (customers which made a deposit and place a bet in the specified date range)  and active non depositors  (customers which placed a bet and didn’t made any deposit in the specified date range).

       

      For one day, the calculated field is working perfectly:

      COUNTD(if [active_date] = [Date]  and [deposit_date] = [Date]

      Then [account_id]END)

       

      When I have a date range it is not working properly. Calculating field that I am using for active depositors:

      COUNTD(if [active_date] >=[First Day Of Month] and [deposit_date] >= [First Day Of Month]

                  and [active_date] <= [Date]  and [deposit_date]<= [Date]

              Then [account_id]

      END)

      Calculating field that I am using for active non depositors:

      COUNTD(if [active_date] >= [First Day Of Month] and [active_date]  <= [Date]

                   and  (ISNULL([deposit_date]) or [deposit_date]> [Date] or [deposit_date]< [First Day Of Month] )           

            THEN [account_id]

            END)

      What is happening is that tableau is not aggregating customers transactions. For example, I have the following data

      Customer ID

      Reporting date

      Deposited date

      Active date

      Deposit amount

      Bet amount

      1

      01-01-2018

      Null

      01-01-2018

      0

      5

      1

      02-01-2018

      Null

      02-01-2018

      0

      10

      1

      03-01-2018

      03-01-2018

      Null

      20

      0

      2

      03-01-2018

      03-01-2018

      03-01-2018

      20

      5

       

      So basically, the date range is from 01-01-2018 till 3-01-2018. What is happening is that with the above calculating fields is that as a result for active depositors the result will be 1 while for active non-depositors the result is 1 too. The expected result should be active depositors 2 while active non-depositors 0 (since customers 1 and 2 bot made a deposit and placed a bet between the starting and ending date).

        • 1. Re: Calculating field to get distinct count of account ID filter on 2 date within a date range
          Yuriy Fal

          Hi Clayton,

           

          Let's simplify the logic a little bit.

           

          We'll be using a FIXED LOD expression

          to aggregate per each Customer ID

          the occurrences of betting (by [Active date])

          and depositary (by [Deposit date]) transactions.

           

          // Active Depositor for the Reported Period

          // For a FIXED LOD calc to get the correct results

          // the [Reporting date] filter should be a Context one

           

          { FIXED [Customer ID] : MIN(

          IF COUNT( [Active date] ) > 0 AND COUNT( [Deposited date] ) > 0

          THEN [Customer ID]

          END

          ) }

           

          // Active Non-Depositor for the Reported Period

          // For a FIXED LOD calc to get the correct results

          // the [Reporting date] filter should be a Context one

           

          { FIXED [Customer ID] : MIN(

          IF COUNTD( [Active date] ) > 0 AND ISNULL( COUNTD( [Deposited date] ) )

          THEN [Customer ID]

          END

          ) }

           

          If performance issues would arise

          (due to FIXED LOD calcs and the datasource size)

          there would be a variation of the calc --

          using INCLUDE LOD instead of FIXED one.

          Of course, the result would be an aggregate,

          not a dimension.

           

          Hope it could help a bit.

           

          Yours,

          Yuri