Metric using Grouping

[hannaglansholm]

We have reports that display the last data for the period, we use metrics that in the Level Target has Month that use Grouping ending(fact) to achieve this. The period can be year or quarter or six-month period, the data is loaded per month.
The report contains both ended periods and present (not ended periods).

The problem occurs when there is no data in some instances. If the metric use ending(fact) the report displays the last loaded data. If the user asks for the period year and December has no data in this instance but the period is ended (say 2001) November is displayed instead. Not correct.
To solve this the metric can use Grouping on ending(lookup) and the report display data for the last period (December data for years) and 0 if there is no data in December. Works well in ended periods. In periods that are not ended (say 2004) the report displays 0, which is not what the users want.

One way to solve this would be to load the database with "0" where there could be but is not data. The database would be huge and we would probably get other problems with that (performance).
I'd like to combine the 2 sorts of grouping to use ending(lookup) in ended periods and ending(fact) in not ended periods!

I'd appreciate any answer, if only to tell me that it wont work! (or that my English sucks...)

 

[wolf5370]

Assume your aggregates are at month. How are you filtering on the month for the report?

Let me explain: We have a similar scenario, but with weekly data. For stock we only want a snapshot - latest position.

If the user runs the report for the last 10 weeks for example, the dynamic dates filter gets last Saturday's date and that minus 70 days (Dynamic dates between). As the aggregate is at week end, we get an accumulation of those 10 weeks for sales, say, where we have a simple Sum metric. For Stock, we use Ending(Fact) - this will now use the last date in the filter and get the fact (stock) for each row. If there is no row on the stock table, then a null cell is returned to the template.

Give us some more info as to your aggregate structure and the report filter.

Cheers,

Wolf