This content has been marked as final. Show 20 replies
In order to create a calculated measure with given expression in AWM, you can use the AWM Calculated Measure template for Calculation Type: OLAP DML Expression.
You can type in your formula in the expression text box and compile it.
STATLEN (LIMIT(LIMIT(DIMTIME to DIMTIME_LEVELREL eq 'WEEK') KEEP MYCUBE_ONHANDASSETS gt 0))
I believe a better expression, more suitable for scenarios with multiple dimtime dimension values present in status (or displayed on the front end report) would be as follows:
STATLEN (LIMIT(LIMIT(LIMIT(DIMTIME to DESCENDANTS using DIMTIME_PARENTREL DIMTIME(DIMTIME DIMTIME)) KEEP DIMTIME_LEVELREL eq 'WEEK') KEEP MYCUBE_ONHANDASSETS gt 0))
This expression would work at WEEK level and above (say, QUARTER/YEAR within the defined dimtime hierarchy).
We want to count the distinct values of the leaf level of one dimension in our cube. We need to be able to count distinct values against all levels of all other available dimensions.
The technique we followed was the one stated in the beginning of this thread.
- Create a new attribute that has 1 for all records in the fact cube.
- Set aggregation to MAX for all other dimensions.
- Set aggregation to SUM for the dimension we want to count distinct values for.
- The order for the aggregation is all MAX are first and SUM is last.
The cube was created succesfully but the results are wrong.
Any suggestion would be greatly appreciated.
You may have hit
BUG 9509395 - MAX AND SUM MIXED OPERATORS IN CC PRODUCE THE WRONG RESULTS
This is fixed in the 188.8.131.52 OLAP B Patch (#10130392)
What version are you using? How exactly are the results wrong? (e.g. numbers too high or too low)
Thank you for your reply.
The version of our db is 184.108.40.206.
The cube results are too high compared to the actual value it should have.
Thank you very much for your assistance. The patch solved our issue and the count works perfectly!
Thanks for letting us know. I'm glad the problem is resolved.