Max(Date) as custom attribute

[JaC74]

There was a similar issue posted last year but I don't think there was a solution to it. Here is the issue:

We have an attribute qualification filter that uses custom between statement with an embedded value prompt:

ApplySimple("year(date(#0) - 2 MONTHS)*100+month(date(#0) - 2 MONTHS)",? [Enter a Value(Date).])

AND

ApplySimple("year(date(#0))*100+month(date(#0))",? [Enter a Value(Date).])

The value prompt has a default value of '12/31/2004'.

We are looking in to removing the value prompt and replacing it with a custom attribute that gets a max (date) from a table. So now, the logic should be:

ApplySimple("year(date(#0) - 2 MONTHS)*100+month(date(#0) - 2 MONTHS)",CustomAttriubte@ID)

AND

ApplySimple("year(date('12/31/2004'))*100+month(date('12/31/2004'))",CustomAttriubte@ID)

The attribute definition is where we are having issues.

It validates ApplySimple("Max(#0)", DT_ID), but the resulting filter fails once it is ran against the DW table because it places a column functin in the where clause.

Any other solutions out there?

Thanks in advance

 

[nlim]

how about using a metric instead of attribute. Create metric as max(date) and add to your applysimple.

 

[JaC74]

Nick, thanks for the quick reply. Actually, I found a easier approach which does not use any MSTR objects in the apply simple. Here is the solution:

ApplySimple("(select year(date(max(DT_LST_UPDT)- 2 MONTHS))*100 from udbadm.RPT_EVNT_STS) + (select month(date(max(DT_LST_UPDT)- 2 MONTHS)) from udbadm.RPT_EVNT_STS WHERE (#0) = 1)",1)

AND

ApplySimple("(select year(date(max(DT_LST_UPDT)))* 100 from udbadm.RPT_EVNT_STS)+(select month(date(max(DT_LST_UPDT))) from udbadm.RPT_EVNT_STS WHERE (#0) = 1)",1)

Works like a charm so far...

 

[JaC74]

I had a needless second pass in both parts of the applysimple. It should be:

ApplySimple("(select year(date(max(DT_LST_UPDT)- 2 MONTHS))*100 + month(date(max(DT_LST_UPDT)- 2 MONTHS)) from udbadm.RPT_EVNT_STS WHERE (#0) = 1)",1)

AND

ApplySimple("(select year(date(max(DT_LST_UPDT)))* 100 + month(date(max(DT_LST_UPDT))) from udbadm.RPT_EVNT_STS WHERE (#0) = 1)",1)

 

[FLB]

Glad you got it to work on your side.

I would still advise against so much hardcoded SQL in the applysimple.... You can get to exactly the same result using "regular" MicroStrategy Objects _and_ this is a lot easier to tune (and sometimes, the MicroStrategy SQL engine will do some good optimization in the way the SQL is generated).

HTH,
FLB