Creating a calculated field with LOD expression

Question

What I have?

purchase_log table with the following fields:

• uid,
• date,
• category,
• amount

What I want?

Visualize the first and second purchases dates for each user from every category.

For example:

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

Considering the above sample data just assume that the first purchase is from category c1 and is made on date d1, and second purchase from category c1 is made on date d3.

I have 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 as there is no distinction between categories, I'm not able to see dates corresponding to categories.

How should I solve this problem?

Answer 1

Go ahead and try the following three LOD's on both the 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])}