FirstSortedValue issue

[#1]

Hi everyone! I would like to get the first product sold to each customer , based on the date of the transaction. The problem is that when more than one products have been sold to a customer on the same date, function FirstSortedValue(ItemCode,DateT) returns null. In that case, I would like to get all the products.

Any ideas?

[Gysbert_Wassenaar]

Something like: FirstSortedValue(aggr(concat(ItemCode,', ')DateT,CustomerID),DateT)

[#3]

Unfortunately, this doesn't work....

[Gysbert_Wassenaar]

Please post an example document.

[#5]

As you can see from the attachment, FirstSortedValue doesn't show records for customer James as he bought 2 products the first date (day 2). I would like to get two records, showing both products for customer James.

Thank you in advance for your help,

[Gysbert_Wassenaar]

FirstSortedValue(aggr(Concat(ItemCode,'|'),CustomerCode,DateT),aggr(DateT,DateT,CustomerCode))

[#7]

Thank you Gysbert, but what I would like is for Customer James to get two records instead of concatenated the two product codes on one record. The reason for that is that I want to count how many customers bought each product for the first time. Hope I was descriptive enough 🙂

[Gysbert_Wassenaar · Accepted solution]

That cannot be done with expressions alone. You'll have add some things in the script. Join the original table with:

join(Original)

Load CustomerCode, min(DateT) as DateT, 1 as Flag

resident Original

Group By CustomerCode;

Then you can use as expression only({<Flag={1}>}ItemCode)

[#9]

Thank you very much!