6 Replies Latest reply on Nov 2, 2016 6:24 AM by Marc MOUSSA

Hi everyone

Could anyone help me with a problem.

I need to get a distinct count of buyers that purchased in the last 7 days but not in the previous 90 days.  These are called lost buyers.  I also need a list of the buyers that bought in the last 7 days but not in the previous 120 days, these are called lost buyers.

So I'd like the number of buyers that are lost and the number of buyers that are lapsed.

Does that make sense?  This is my formula.

IF(SUM(IF [Date Sold]>=[Week Ending]-365
AND [Date Sold]<=[Week Ending]-121
THEN [Number of Records]
END)>0)

AND

ISNULL(SUM(IF [Date Sold]>=[Week Ending]-120
AND [Date Sold]<=[Week Ending]-8
THEN [Number of Records]
END))

AND

SUM(IF [Date Sold]>=[Week Ending]-7 AND [Date Sold]<=[Week Ending]
THEN [Number of Records]
END)>0
THEN "Lost"

ELSEIF

(SUM(IF [Date Sold]>=[Week Ending]-365
AND [Date Sold]<=[Week Ending]-91
THEN [Number of Records]
END)>0)

AND

ISNULL(SUM(IF [Date Sold]>=[Week Ending]-90
AND [Date Sold]<=[Week Ending]-8
THEN [Number of Records]
END))

AND

SUM(IF [Date Sold]>=[Week Ending]-7 AND [Date Sold]<=[Week Ending]
THEN [Number of Records]
END)>0
THEN "Lapsed"

END}

Any help appreciated

• 1. Re: Buying in different time frames

Hi Jonathan!

WOW-- that's quite the mouthful of an equation you have there. And an LOD, no less!

I suggest baby steps-- break the calc apart into more manageable pieces, see if they work, before stringing them together again.

Create independent LOD expressions for LAPSED and LOST customers and put them side-by-side to see if they are generating the results you expect.

Perhaps you could also post a small TWBX so those in the forum could check your logic, too?

• 2. Re: Buying in different time frames

Yeah I have, but I just cant get it to work...I hate tableau sometimes (most times)

• 3. Re: Buying in different time frames

Can you send us a small TWBX so we can see how it is working (or not working) ?

• 4. Re: Buying in different time frames

Why don't you use sets and combined sets to get what you want it would be easier than LODs to get what you want.

• 5. Re: Buying in different time frames

Possibly but as a Tableau novice I don't yet know how.  Can you explain?

• 6. Re: Buying in different time frames

Can you post a small TWBX workbook ? It would be easier to explain as you'll get also a workbook with all the calculations.