I'm not sure the best way to put this into words, so I wrote it out in what I hope is an English legible pseudo code. # denotes a comment for explanation purposes. I've been trying to do this in tableau with LOD descriptions, for example {FIXED [Subscriber ID]:COUNT([Subscriber ID])} counts the number of instances of the same ID but it doesn't exclude those not made within 24 hours of one another. Any help would be greatly appreciated
for each entry:
get list of all other entries with same UserID
for each entry in list:
if thisEntry.date-entryWithSameUserID.date < 24 hours:
add to resultsList
break
⌗now I have a results list with a bunch of entries that were logged
⌗within 24 hours of one another from the same user ID
⌗note that if a user made 2 entries in our data base on 1/1/2019
⌗and then the same user made 2 entries in our data base on 2/2/2019
⌗the same user would be in our results list multiple times for the
⌗two seperate occasions that s/he made multiple purchases within 24 hours
for each entry in resultsList
⌗get how many entries were made within a 24 hour period per ID
Count instances of each ID
⌗get which purchases led to more purchases within 24 hours
⌗ex. if someone buys a puppy, are they likely to also buy a bed within 24 hours?
⌗ex. if someone buys a puppy, are they likely to also buy a bed AND food within 24 hours?
for each set of purchases made within 24 hours
count which purchases were made with the earliest date
The relative date will consider the computer date (unless you specify at the bottom that the date is relative to a specific date).
But this can cause problems if your data is not updated. If someday you don't have your data refresh done the dashboard will appear empty (which is never good).
To address that I had to apply something like this LOD on a dashboard I built:
{FIXED [Dimension] :
SUM(IIF(DATETRUNC('day', [Date]) = [MAX Date],[Measure],NULL))
}
And the MAX Date field is just a check on your MAX(Date) (which you can define as anything you want). For me it looks like this:
DATE({MAX([Date])})
I hope these helps.
Rodrigo
