Last occurrence set analysis

[#1]

My transactions have a [transaction type].

I want to count how many transactions each customer has had since their last [transaction type] = 3.

I'm quite lost, to be fair.

Breaking out down, I can get the last "3"  transaction thus:

max({< [transaction type]={3} >}  [order time])

But how to then say "count everything after the order date from this expression". ??

I've been playing all day with aggr and other functions, but think I'm just getting into a bigger mess.

Any help?

[marcus_sommer]

You could try something like this:

count({< [transaction] = {"=[order time]>max({< [transaction type]={3} >}  [order time])"}>} [transaction])

- Marcus

[#3]

Thanks Marcus, your code makes logical sense, but I'm still struggling.

I decided to simplify my data,  to try and see clearer.
I'm now identifying each row with an incremental id  (effectively encompassing id and time into 1 field)   

Tran	tranName	tranType
1	Apples	3
2	Apples	3
3	Apples	1
4	Apples	3
5	Apples	1

So my new expression (not working) is ...
Expression = count( {<[Tran] = {  "> max( {<[tranType]={3}>} [Tran] )"  } >}  [Tran] )

Dimensions used = [tranName]

My result is just '0'.

I did a second expression with just  max( {<[tranType]={3}>} [Tran] ), and that gave me '4'.

I'm not sure if I am getting syntax, or the whole concept wrong !!

[sunny_talwar · Accepted solution]

May be this

Sum(Aggr(If([order time] > Max(TOTAL <Customer>{<[transaction type] = {3}>} [order time]), 1, 0), customer, [order time]))

[mangalsk]

Hello,

Try this if works else share data and exact expected result

count({<[order time]={">=max({< [transaction type]={3} >}  [order time])"}>}[transaction ID])

[marcus_sommer]

Try it with changing the > to >= to see if it made a difference:

count( {<[Tran] = {  ">=max( {<[tranType]={3}>} [Tran] )"  } >}  [Tran] )

- Marcus

[#7]

Thanks so much Sunny, seems to do the job  nicely !

I'll sit down and make sure I understand It properly, add to my learning;  but for now, it's doing what it should

[#8]

Mangal,

Thanks for your efforts, 'unfortunately' Sunny's suggestion 'just worked'.

Maybe some nuance of my data ?

Appreciate your suggestions, and as a result, I will at least go learn a bit more about those nested statements.

[08748190]

Hello , 

Can you give me an example for the last occurence without user for the function max

for example :

This salary  work for 100 % full time and after 60% 

so i want to get of 60%, 

Set analysis :

( sum (  {$<

   [__FTE]={">= max({<__Period_id = {'$(vdate_analyse)'}>}[__FTE])"},

   [__Contract_Type]-={'IN','PS','ST','TV'}

   >}  distinct [__FTE]) )

With :

FTE : full time percentage (100.00 or 60.00,...)

Thank you in advance