Date Calc

[petperson]

I need a metric to calculate # of Days Enroute. I need it calculate the number of days to present date if it has not yet been received. For example, if the enroute date is 12/10 and as of today (sysdate) it has not been received, the # of days enroute = 2. If it has not been received tomorrow, the # of days enroute would be 3. I have an Enroute Date and Received Date attribute. I'm not exactly sure how to get all of this in the apply simple statement. Any suggestions?

Thanks!

 

[entaroadun]

Could you explain your table structure and give a sample report?

 

[petperson]

PO Received_Dt is in a Summary fact table which rolls up from the Purchases table (Keys are Day_dt, PO_Nbr, Vendor, Location). Enroute Dt is also in a higher level purchases table -same keys.Here is a sample of what I'd like to see based on today's date (12/12):

Ven PO Enroute Dt # of Days Enroute Recd Dt
1 46 12/10/2003 2
2 56 12/1/2003 4 12/5/2003

 

[entaroadun]

In a metric:

ApplySimple("(case #2 is null then (sysdate - #1) else (#2 - #1) end)",[Enroute Date]@ID,[Received Date]@ID)

Substitute whatever date diff function you may need for your RDBMS for the subtration operators.

This will work as long as you have both Enroute Date and Received Date in your report template.

 

[petperson]

thanks, I tried this - keep getting 'attribute not allowed in this place' error. I will try it from facts on the dates. Not sure why it won't let me use attributes?

 

[entaroadun]

In order for a metric to resolve in a report, it must have dimensionality. A metric is an object that contains instructions on how to take a schema object (such as a fact), and "aggregate" it, or resolve it, to the level of the report (otherwise known as ReportLevel). The level of the report is determined by the intersection of the lowest level attributes on the template and filter.

ApplySimple provides no dimensionality. In a metric, it only works if you pass in objects that already have dimensionality, such as other metrics. In this case, the dimensionality has to be the same in order for the ApplySimple to work. (If they were different, MSTR wouldn't know how to integrate them.)

Therefore, try using ApplyAgg. ApplyAgg provides dimensionality. You don't need to change the structure of your statement; just swap out ApplyAgg for ApplySimple.

The other part is the entry level of the schema objects you are passing in. Facts have an entry level; it defines the level at which the fact is stored. The metric contains instructions on how to aggregate the fact up to the ReportLevel.

When used in a metric, the entry level of an attribute is itself.

In order for a metric expression to resolve, the entry levels of the objects within it must be related, so MSTR can integrate them. If they are totally unrelated, MSTR cannot resolve the expression.

Therefore, I'm pretty sure that in your case, using ApplySimple or ApplyAgg with the attributes won't work. Only the facts, which should have the same entry level, will work.

I'm pretty much self-taught with MSTR; I've tried to deduce its behavior. I'm curious to see if I'm right. Try ApplySimple with the attributes and facts and ApplyAgg with the attributes and facts. Of the four, I'm willing to bet that only ApplyAgg with the facts will work. If one of the others does work, please post the SQL that's generated.

 

[nlim]

petperson, i have a low tech suggestion which might help.

1) create a metric M1 defined as max(received date)
2) M2 = max(enroute date).
3) M3 = M1-M2
4) M4 = currentdate() - M2
With your report structure, you will likely get something like this

Ven PO M2 M3 M1
1 46 12/10/2003 null
2 56 12/1/2003 4 12/5/2003

You should be able to create a derived metric which checks for if M3 is null, then show M4, else M3.

 

[entaroadun]

Yes, assuming that enroute date and received date are on your report, this will work perfectly.

 

[petperson]

Thanks for all the suggestions. I'm still having a problem getting this to work. NLIM, this is the result of your suggestion. I'm displaying 2 different PO's. The report is displaying the date attributes as well as the date metrics. The PO without the null value for Received Dt works fine all around. The PO with the null value gives me all zeros for all metrics. I've tried playing around with VLDB null parameters, join parameters..etc. I can take this SQL and by removing the join on Received Dt in the last pass (it joins to temp tables) I can get the result I want. I just can't seem to make MSTR do this. I'm open to more suggestions! thanks

PO EnrteDt Recd Dt EnrteDt(M1) Recd Dt(M2) M3 M4
4 12/27/2003 12/29/2003 12/27/2003 12/29/2003 2 4
5 12/29/2003 (null) 0 0 0 0

 

[nlim]

it looks like you have the 2 date attributes on your report. I would remove them and just place PO (attribute), M1, M2, M3, M4 on the report.

If you add the dates to the template, the sql tries to find entries with the same attributes, hence the unwanted join.