Alternate state for if clause for a calculated dimension

[amaaiia]

Hi!

I want to create a new calculated dimension like this:

1. I have the following two tables.

   TableA:

   ID	DATE1
   123	02/01/2022
   456	01/12/2021
   789	20/01/2022

   TableB:

   ID	DATE2	DATE3
   123	01/01/2022	31/01/2022
   456	15/01/2022	15/02/2022
   789	01/02/2022	01/03/2022

2.  There is a calendar with alternate state A with DATE1 field. I want to create the dimension NEW_DIM son each ID has the value 'A' when max({A}DATE1) is between its DATE2 and DATE3, otherwise, 'B'. I have tried with if(DATE2<max({A}DATE1) and max({A}DATE1)<=DATE3, 'A', 'B), but it gets an error. I can replace {A} with TOTAL and it works but it doesn't take into account tha max selected value in alternate state A, but the max DATE1 of all the data.

Any solution?

Thanks.

[marcus_sommer]

The error happens because you used an aggregation within the expression without wrapping it within an aggr(), like: aggr(if(DATE2<max({A}DATE1) and max({A}DATE1)<=DATE3, 'A', 'B), ID) whereby it's not clear if you really need those max() and/or if this construct might need an extended dimensionality.

Beside this it looked for an unsuitable data-model because I would tend to merge all date-information into a single field and avoiding alternate states and such calculated dimensions ...

- Marcus