Date Manipulation Help - Needed Soon!

[andi36]

Using MSTR to find number of days from when a subscriber had a service call (date of service) to when the invoice was paid (Invoice Payment Date). The dates are modeled on separate tables in Microstrategy (the modeling battle was fought and lost so it's relatively static now).

I am coming from using abInitio for 3 years back to having to develop in MSTR so I am rusty. Backend is DB2. Maybe ApplySimple? However, my syntax is totally off...

Please help - I will resort to bribery if I have to.

Andi

 

[nlim]

assuming that the 2 dates are somehow linked by subscriber and servicecallworkorder, suggest you try the following.

- create 2 facts modeled on the 2 dates, update schema.
- create a metric defined as sum(invoicedate){~,servicecallworkorder,subscriber} - sum(servicecalldate){~,servicecallworkorder,subscriber}

should work is sql. in db2 date1-date2 gives you an integer of the number of days inbetween.

 

[NaughtyVicar]

See TechNote TN5200-7X0-0019 for syntax on ApplySimple. It gives an example (example 2)based on SQL Server using the datediff function (SQL Server) and ApplySimple. I don't know the DB2 function but this note might provide some insight.

Other TechNotes of interest:
TN5200-7X0-0131
TN4000-7X0-0375 (in case you want to prompt for a value)

There's not alot of help with the Apply functions in MicroStrategy's documentation, but if you use these notes as a starting point to understanding the syntax and experiment a bit, you should be able to figure it out.

Sorry if this wasn't more help.

Good luck! Post your results when you get it working...

-Rich