Dates In Prompts - Using Results of Prompt Input ?? 1

[zxzzzzzz]

Here is what I need to do:

My users can input one month or a range of months using the simple prompt - BETWEEN statement on my field week_ending_date. Once they input date/date(s), I need to be able to do a MIN of the dates they input as well as a MAX of the dates they input. This is needed to retrieve a true beginning of week inventory and true end of week inventory figure instead of average figures for the whole date range ((want beginning inventory to be from first input week and ending to be from last input week). I can do this is SQL+; however, MSTR is not letting me use MIN/MAX on a date datatype.

Suggestions??? Thanks !!

 

[nlim]

the traditional and most generic way to do this is to create a &quot;non-aggregatable&quot; metric. Very easy. if your beginning inventory metric is M1 sum(inventory_column), then make sure that you set the dimensionality to be at the report level, and add in a &quot;week&quot; dimensionality. For the grouping it should be beginning (lookup). your full metric definition should be Sum([inventoryfact]) {~+, <Week+}

Do the same for your ending on hand except choose ending (lookup) for the grouping.

This way is best because the beginning and ending inventory metrics will work for all reports, not just this specific one you are creating. The min and max is automatically generated for you in the sql. This is what MSTR is good at and known for.

 

[zxzzzzzz]

I'm still fairly new to Microstrategy (Our actual installation is not until October of 2003--we're on demo still!), and I'm not 100% familiar with &quot;grouping&quot; and &quot;Lookup&quot;. Is this custom grouping?? (I can look it up from there).

Thanks! Sorry.... :~/

 

[nlim]

nope. in the metric editor window, look for something that says &quot;dimensionality&quot;. click on it and you'll see what i mean.

you are trying something really advanced for a demo stage...i'm glad you chose to try MSTR (unless you are working for one of their competitors) MSTR is good for these types of complicated analytics. Don't pin your hopes too much for nice formatting tho...

also would recommend you take the training...it's the only way to appreciate and use the tool well.

 

[zxzzzzzz]

Actually I will be the Administrator as well as report creator. Luckily I'll be able to take Administrator and report writing training before we install. We are doing a &quot;proof of concept&quot; or basically trying to sell MSTR to our business users (We are retail sporting goods company). Any advanced reports I can give them will only help sell the product when it comes time for end user training.

Thanks SO MUCH for your help. You really know the product well!

 

[nlim]

i would then suggest you ask your business user for the &quot;nirvana&quot; report that they want but have not been able to get. Create that in MSTR and you will sell them.

Good luck