Is there a way to do (max,-1)?

[#1]

Basically I want to know is how many customers went from a fixed rate contract to a month to month contract then back to a fixed rate contract. The way I decide which contract is which is with something like:

if(max([Contract Start Date],1) = 'Fixed Rate' And max([Contract Start Date],2)='Month to Month' And max([Contract Start Date],3) = 'Fixed Rate'

The problem is that this will only tell me if their LAST contract was a fixed rate and their second to last contract was month to month and their third to last contract was a fixed rate. Let's say they went from fixed rate -> month to month -> fixed rate -> fixed rate (renewing that contract), this customer would not show up using my method above. However, I want that person to count because I want to know how many customers go from fixed -> month to month -> fixed, regardless of what contracts they have before/after this.

It is important to note that I cannot access the script or use any macros b/c this is on a server and I'm not an IT guy. If anyone knows of any ways to do that, it would be great. I'm assuming there's a way with variables, using like n and n-1 and n-2 but I can't create variables so that wouldn't be great. Technically I COULD export all of the customers to Excel and write a macro in VBA... but it would be incredibly time consuming to export all of the customers and all of their contract information into Excel and I'm not very good in VBA so that code would likely be pretty tough for me to write.

Appreciate any help.

[johnw]

Assuming you want customers included even if they then switched back to Month to Month later, this is what I have working in the attached example for a flag:

if(index(concat(aggr(if(Type<>above(Type),Type),Customer,Start),',',Start),'Fixed,MTM,Fixed'),'Yes','No')

And for a count:

sum(aggr(if(index(concat(aggr(if(Type<>above(Type),Type),Customer,Start),',',Start),'Fixed,MTM,Fixed'),1),Customer))