Nested selection in formula

[#1]

Hi folks,

this discussions were always helpful but unfortunately I encountert a problem that I couldn't solve myself nor by checking here.

I need a formula which shows me a entity in a dimenson of a diagramm which hast a [dateto] which is one day less than the [datefrom] from the entity with the ultimo [dateto] (as implemented in my database as '31.12.9999'). This is a Pseudo-Code to show what I want, not my approach in the formula editor, which I rather ingore because I suppose it's rather confusing.

[entity]        WHERE
[dateto]       EQUALS

               [datefrom] -1   WHERE

               [dateto]          EQUALS

                         '31.12.9999'

I'd like to thank everybody who's looking at my problem in advance and maybe someone can help me.

Thanks

Markus

[brenner_martina]

Hi, Markus,

I think your formula is not possible, because you say dateto = datefrom-1 AND dateto='31.12.9999'? You can in one field only get one equalance!

Greetings from Munich

Martina

[#3]

Can I store a subset then?

Or in another approach I rather show where I'm going with all this (and my approach is nonsense or not elegant). My data looks something like this:

[ID];     [DATEFROM];      [DATETO];      [FACTOR]

1;     01.01.2000;     12.01.2006;     1,234

1;     13.01.2006;     10.04.2008;     2,050

1;     11.04.2008      07.07.2012;     2,345

1;     08.07.2012;     31.12.9999;     1,456
2;     03.05.2006;     18.03.2010;     23,45
2;     19.03.2010;     20.12.2011;     25,00
2;     21.12.2011;     31.12.9999;     21,22

3;     16.10.2011;     31.12.9999;     5,111

3;     05.01.2007;     15.10.2011;     4,999

So I need to compare the last current two [factor] to each other, without knowing how much entries each [ID] has and in which order they apper. Also the date of the single previous entity isn't know but can be calculated by [datefrom]-1 of the [dateto] = 31.12.9999 entity.

[brenner_martina]

Hi, Markus

What about this in script?

Left Join (Data)

LOAD ID,

   Max(DATETO) As MaxDate

Where DATETO <> '31.12.9999'

Group By ID;

Than you can work in your expression like this:

If(DATETO = MaxDate, FACTOR)

Greetings from Munich

Martina