0

If I want to find the maximum value of a column from two states aggregated by a member's ID, should this work?

=Aggr(
    MaxString(
        Aggr(NODISTINCT MinString({[State1]}DATE_STRING),MBR_ID)
        +
        Aggr(NODISTINCT MinString({[State2]}DATE_STRING),MBR_ID)
) , MBR_ID)

So if I had this data:

MBR ID          DATE_STRING
1               20120101
1               20120102
1               20120103

And State1 had 20120101 selected and State2 has 20120103 selected, my expression would return 20120103 for member 1.

Thanks!

Edit: In SQL, this would look like:

WITH MinInfo (DATE_STRING, MBR_ID)
AS (SELECT MIN(DATE_STRING), MBR_ID FROM Table WHERE TYPE IN ('State1', 'State2') GROUP BY MBR_ID, TYPE)
SELECT MAX(DATE_STRING) DATE_STRING, MBR_ID FROM MinInfo GROUP BY MBR_ID
Bret Walker
  • 1,796
  • 5
  • 20
  • 41

1 Answers1

0

It would be easier to accomplish your goal if you convert your that to an actual date field Assuming that you are using a chart where MBR_ID is the Dimension, if you want the maximum date (latest date) you can do the following:

=nummax(Max({[State1]}DATE_STRING),Max({[State2]}DATE_STRING))

To convert to a date, you can use this function:

date#(DATE_STRING,'[text format of the date]')

(The date format looks like YYYYMMDD to me, but if its day then month, you would use YYYYDDMM)

I'd suggest you format it in the script, so that you wont have to worry about it every time you need to use that date.

JMon
  • 43
  • 6