BEx Exception Aggregation Minimum for (calculated key figure) type DATE

[HeikoGottwald]

Hi folks,

we do have the follwowing problem, probably someone may help us:

- Key figure type DATE, exception aggregation MINIMUM

- BEx Query: Aggreation within the BEx-Query at a hierarchy: Zero-values are transferred as smallest (MIN) values to the top of the hierarchy, but is wrong, as only "real" values should be aggregated

- a calculated keyfigure was created, regarding to the BW-keyfigure and additionally with exception aggregation (aggregate all values except 0), but this setting only fits for amounts or quantities, no dates ...

Does anyboday has had the same problem and may offer a possible solution?

Your help is very appreciated,

Thanks!

Cheers, Heiko

[Former Member]

Hi.

I am not sure I completely understand what you are trying to do, but did you try "zero-suppresion" to get rid of the 0's ?

br

jacob

[HeikoGottwald]

Thanks for your reply, Jacob!

We do have ...

within the query-definition: a bw-hierarchy and a keyfigure with zero-values (00.00.0000 the zero-surpression does not work for this format.) and "normal" values like 23.05.2009 -> bw-keyfigure type DATE, exception aggregation MINIMUM in order to aggregate the smallest date onto the top hierarchy level.

the exception aggregation does work, it aggregates the smallest value .... which is 00.00.0000 -> thats the problem.

we also created a calculated keyfigure with different exception aggregation, but without any success... it seems that it is not possible to aggergate (with exception aggregation MIN) dates ...

if you have any idea i really appreciate a hint!

Thanks a lot!

Heiko

[Former Member]

mmmhhh... okay... still don't think I understand what you are trying to do, other than that you want to get rid of KF values = 00.00.0000, so how about a condition on the date KF, setting it to not equal 00.00.0000?

....maybe using a condition will not help... I am a little confused about how you are using the exception aggregation MIN and the hierarchy, because it sounds to me like you want to SORT the values of the KF and not see the 0's.

On the other hand, you might be trying to get a report that shows you the FIRST date of some kind of event that has multiple occurences and hence multiple dates (including 00.00.0000). In that case, you could add a characteristic to your cube, which you populate in the load with an X, if the record resembles the first occurence of the event and use that in your report.

br

jacob

[neelesh_jain3]

Try Creating a CKF with formula:

(DATE = '00.00.0000') * 0 + (DATE <> '00.00.0000') * DATE.

Cheers,

Neel.

[HeikoGottwald]

Hi there,

many thanks for your answers, I will test the proposed anserws which seems to fit!

Best Regards, Heiko