Tableau - Conditions on LOD Expression

Question

I have a purchase_log table which has the following fields:

uid, date, category, amount

And I would like to know the first and second purchases dates for each user of each category.

For example:

+-----+------+----------+--------+ 
| uid | date | category | amount |
+-----+------+----------+--------+
| A   | d1   | c1       | 100    |
| A   | d2   | c2       | 200    |
| A   | d3   | c1       | 120    |
| A   | d4   | c2       | 300    |
+-----+------+----------+--------+

For above user records, I would like to say the first purchase from category c1 is made on date d1, and second purchase from category c1 is made on date d3.

I currently created 3 calculated fields:

1st purchase:
```
{ FIXED [uid] : MIN([date])}
```
Repeat purchase:
```
IIF([date]>[1st Purchase],[date],null)
```

2nd purchase:

{ FIXED [uid] : MIN([Repeat Purchase])}

But since there is no distinction between categories, I'm not able to see dates with respect to categories.

How should I solve this problem?

Thanks.

score 3 · Answer 1 · edited Sep 21 '15 at 15:38

3

You can do so by LODing on both uid and the category.

1st purchase:

{ FIXED [uid],[category] : MIN([date])}

Repeat purchase:

{ FIXED [uid],[category]: IIF([date]>[1st Purchase],[date],null)}

2nd purchase:

{ FIXED [uid],[category] : MIN([Repeat Purchase])}

edited Sep 21 '15 at 15:38

Andrew LaPrise

3,373
4
32
50

answered Sep 21 '15 at 12:56

Charlie Haley

4,152
4
22
36

Hi Charlie, thank you for your answer. However on repeat purchase I'm getting "Result of a LOD expression must be an aggregate" error. How should I fix that? – CanCeylan Sep 22 '15 at 13:43
@CanCeylan try { FIXED [uid],[category],[date]: IIF([date]>[1st Purchase],[date],null)} Adding date to the LOD should reduce the granularity of aggregation to the date level. – Charlie Haley Sep 22 '15 at 13:48
tried that one as well, but still getting the same error and in underlines the IIF statement. – CanCeylan Sep 22 '15 at 14:06
If you surround the IF statement with a function like MIN(), does it work/give you the proper behavior? – Charlie Haley Sep 22 '15 at 14:08

Tableau - Conditions on LOD Expression

1 Answers1