why my calculated cant be applied in my filters tableau

Question

this is my tableau workbook

so i want to calculate day different between each transaction for each users, the users on this case are in filters PUL: True with this calculation

{Fixed [User Id]: sum(
if [Created At]<=[END_DATE] then 1 else 0 end)}>=2
AND
{FIXED [User Id]: sum(
IF [Created At]<=[END_DATE] AND 
[Created At] >= [START_DATE] THEN 1 ELSE 0 END)}>=1

it means the users with more than 1 transaction before last range and atleast doing 1 transaction in date range are on the list.

so after that i made calculated field to count day different with this formula [CF]

DATEDIFF('day',LOOKUP(MIN([Created At]),-1), MIN([Created At]))

and also i made filters date range

lookup(min(([Created At])),0) >= [START_DATE] and 
lookup(min(([Created At])),0) <= [END_DATE]

so not only count the time difference in date range for selected users, but each users also count day different on last transaction before date range (if any)

so this is the results (please take a look at user_id 86886)

i didnt understand why user_id 86886 as my experiments only have 1 transaction instead i already made the filters that only take user who doing transaction more than 1, after i check it, that user_id 86886 are doing more than 1 transaction in a day. this is the screenshoot

my questions is

1. why the tableau cant visualize all of the transaction in same day (but different hour) like this

and how to visualize it so it will appear 2 records with same day but different hour.

2. and also why if the transaction on the same day count time different with NULL instead it should be 0 because there's no time different

EXPECTED RESULTS (let's take a case in user_id 86886)

+---------+-----------------------+-------------+
| user_Id |    dayOfCreatedAt     | CF diff day |
+---------+-----------------------+-------------+
|   86886 | 1/25/2020 11:25:28 AM |             |
|         | 1/25/2020 11:39:42 AM |          0  |
+---------+-----------------------+-------------+

explanations : the first one of CF diff day become NULL because it's first transaction of this user, and the user is not doing transaction again before that, and the second was "0" because there's no day different, the different was only in hour, but although it was same day, so there will be not different day and it count 0

based on @Anil advice, this is my link of workbook tableau https://public.tableau.com/profile/fachry.dzaky#!/vizhome/simulation_data/Sheet14

Answer 1

I think your PUL field has some errors. Probably you should change the calculation of this field as

{Fixed [User Id]: sum(
if [Created At]<[START_DATE] then 1 else 0 end)}>=1
AND
{FIXED [User Id]: sum(
IF [Created At]<=[END_DATE] AND 
[Created At] >= [START_DATE] THEN 1 ELSE 0 END)}>=1

Because, if you are trying to get non-null difference there must be at least one transaction before the [start_date]. Check it please.

Now please follow these steps (carefully please)

Step-1 Drag user id and created at at Rows Shelf.

Step-2 Change created at to exact date and thereafter to discreet (Both are important and you view shows that you haven't displayed it as exact date and as day of created_at instead)

Step-3: Set you date parameters

Step-4 Drag all three desired fields to filters shelf.

Step*5: Add PUL to context (again an important step)

Step-6 double click the CF field (check its calculation as

DATEDIFF('day', LOOKUP(MIN([Created At]),-1), MIN([Created At]))

Step-7 Change table calculation options of CF as table down (check this is also important)

Step-8 double click other CF_max/Min fields to add these to measure values

Step-9 change table calculation options in each of these four fields as discussed earlier (i.e. nested calculation of CF as specific dimensions restarting at every user_id). And nested calculation of CF_Max(as the case may be) to table down.

NOte Regarding you specific problem of user_id 86886 (if I will remove PUL revised condition as TRUE I am getting the same view as desired). Have a look please