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

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

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