Calculated measure using Tail/Filter on time not always working

We created the following time based measure to get the distinct count of propertyId - but we needed to get the distinct count from the last fact table load in a time period so we used the following

Code Snippet

IIF([Time].Currentmember.level.ordinal = 4, [Measures].[Property Id],

IIF([Time].Currentmember.level.ordinal = 3,

(tail(filter([Time].Currentmember.children, NOTISEMPTY([Measures].[Number of Buildings])), 1).item(0), [Measures].[Number of Buildings]),

IIF([Time].Currentmember.level.ordinal = 2,

IIF([Time].Currentmember.properties("Is Quarter") = "1",

(tail(filter([Time].Currentmember.children, NOTISEMPTY([Measures].[Number of Buildings])), 1).item(0), [Measures].[Number of Buildings]), ([Time].Currentmember.lastchild, [Measures].[Number of Buildings])),

IIF([Time].Currentmember.level.ordinal = 1,

IIF([Time].Currentmember.properties("Is Year") = "1",

(tail(filter([Time].Currentmember.children, NOTISEMPTY([Measures].[Number of Buildings])), 1).item(0), [Measures].[Number of Buildings]), ([Time].Currentmember.lastchild, [Measures].[Number of Buildings])),

IIF([Time].Currentmember.level.ordinal = 0,

(tail(filter([Time].Currentmember.children, NOTISEMPTY([Measures].[Number of Buildings])), 1).item(0), [Measures].[Number of Buildings]),

NULL

)))))

In most cases, the calculation seems to work fine, but sometimes, when browsing the cube using Proclarity (6.1), no values are returned when we know there are values...

Can anyone see why this measure would not work in some circumstances?

I think that basically, what I'm asking for is a method of determining a distinct count of the last recordset from a fact table for any time dimension level (which is what the above is trying to do).

The above method seems only to work rather haphazardly, especially when viewing via Proclarity (6.1) When you move certain dimensions to the background, the calculation returns zero when we knbow data exists...

Is there a more efficient way of finding this sort of distinct count (using SQL 2000) ?

|||Anyone fancy a stab at this ?|||

Hi,

have not read the whole mdx,

do not know proclarity

This does not work if you do a multiselect on the time dimension beause of CurrentMember (there is no current member if you have a subquery)

Does it return null or #error and do not show up.

It really depends on how your client does write his queries. I would recomend you trace your queries with profiler and then analyse the query which is returning wrong results. Check where and how the measure is used and check if there are any subqueries based on time.

Best Regards, HANNES

|||

Thanks for having a look at this

The following query will not return any values for No of Buildings

Code Snippet

SELECT { [Measures].[Number of Units], [Measures].[Number of Buildings], [Measures].[Property Id] } ON COLUMNS ,

{ DESCENDANTS( [Building Status].[All Building Status], [Building Status].[Building Status] ) } ON ROWS

FROM [Property]

WHERE ( [Time].[Year].&[2007] )

I can't really see why not, as it should pick up the latest data for 2007 should it not, given the calculations in my first post ?

|||

Hi,

I am a little bit confused about the complexity...

A few more questions.

Do you have only one Hierarchy in Time? (All Attribute hierarchies are hidden - I guess yes because you do not specify an hierarchy name)

Is it a 2005 cube or 2000?

Are the properties populated correct? Is Quarter, Is Year

Why you need this properties - if its one hierarchy then level 2 could only be quarters? and level 1 only years and level 0 only ALL if I am right

Why do you write this complex form - why not only decendants(time.currentmember),time.levels.count, leaves) which your tail logic

maybe one of this is a hint to you.

HANNES

PS: I would alos recommend moshas blog with design time level checking instad of runtime level checking. Scope instead of member with iif (only for design purpose - have nothing to do with functionality)

|||

Hi,

1. SQL 2000 cubes - and yes, the properties are all fine and we can't change time dimension at this stage as it works fine itself.

It's this complex because we have daily loading in certain parts of DW but weekly in others - in terms of a measure using distinct count

this presents issues when querying measures from weekly loads in terms of needing to hit the last load in a period, be it month, quarter, year or All Time...

In any case we fixed the issue by creating a completely new view for building count as we read that distinct count runs in to known issues when AGGREGATE used in MDX queries, so in developing the new view we populated a 1 for propertyid as opposed to attempting to do a distinct count - also we created calculated cells to deal with the time hierarchy levels issue - we then based a new cube containing just building count but shared same dimensions with the old cube, containing other aggregateable valid measures that worked.

Megred the two cubes into a new virtual cube & now building count works fine So problem solved. I think the tail/filter issue was a red herring in any case - the data that got was fine - AS 2000 just does not like distinct counts (in the way we were trying to use them...)

Thanks for getting involved with this though, but i'll mark it as solved for now.

calculated DistinctCount over two levels of "incomplete" weeks

Hello all,
I have a cube on as2005 where my Time dimension has 5 levels(year, month, quarter, week, day) and a measure that is a distinct count of customers (I also have a customer dimension, with 1 level: customer)

In my time dim, I have "incomplete" weeks, for example the week 32 exist on both august and september months, 5 days of the week in august and 2 days in september.
So at week level I get the distinct customers for that week, for example:
month | week | # distinct customers
AUG | 32 | 15
SEPT | 32 | 20

but, since the week is the same, i want to get the distinct customers for the whole week, and put it on the last month where that week appears, in the example let's say that for week 32 there are 18 different customers, I want a 18 on SEPT.

month | week | # distinct customers
AUG | 32 | NULL
SEPT | 32 | 18

Someone know how can I achieve this?
Thanks!I've found the way to do this, I hope this helps the community!

Aggregate
(
UNION([DM Time].[Hierarchy].CurrentMember.PrevMember.Children,[DM Time].[Hierarchy].CurrentMember.Children)
,[Measures].[# Customers]
)

only for the scope of the week.

Calculate % qry

Hi,

I am trying to execute a query which calculate % on group by. here is the query

SELECT C.USR_HIGHEST_DEGREE,COUNT(DISTINCT C.MASTER_CUSTOMER_ID) AS [# MEM],(convert(numeric(5,2),COUNT(DISTINCT C.MASTER_CUSTOMER_ID))

/ (

SELECT convert(numeric(5,2),COUNT(CC.MASTER_CUSTOMER_ID))

FROM MBR_PRODUCT AS MPP WITH (NOLOCK) INNER JOIN
PRODUCT AS PP WITH (NOLOCK) ON MPP.PRODUCT_ID = PP.PRODUCT_ID INNER JOIN
ORDER_MASTER AS AA WITH (NOLOCK) INNER JOIN
CUSTOMER AS CC WITH (NOLOCK) ON AA.SHIP_MASTER_CUSTOMER_ID = CC.MASTER_CUSTOMER_ID INNER JOIN
ORDER_DETAIL AS BB WITH (NOLOCK) ON AA.ORDER_NO = BB.ORDER_NO ON MPP.PRODUCT_ID = BB.PRODUCT_ID
WHERE (CC.CUSTOMER_STATUS_CODE = 'ACTIVE') AND (BB.CYCLE_END_DATE >= GETDATE()) AND (AA.ORDER_STATUS_CODE = 'A') AND
(BB.LINE_STATUS_CODE = 'A') AND (BB.FULFILL_STATUS_CODE IN ('A', 'G')) AND (MPP.LEVEL1 IN ('NATIONAL'))

)*100 AS [%]

FROM MBR_PRODUCT AS MP WITH (NOLOCK) INNER JOIN
PRODUCT AS P WITH (NOLOCK) ON MP.PRODUCT_ID = P.PRODUCT_ID INNER JOIN
ORDER_MASTER AS A WITH (NOLOCK) INNER JOIN
CUSTOMER AS C WITH (NOLOCK) ON A.SHIP_MASTER_CUSTOMER_ID = C.MASTER_CUSTOMER_ID INNER JOIN
ORDER_DETAIL AS B WITH (NOLOCK) ON A.ORDER_NO = B.ORDER_NO ON MP.PRODUCT_ID = B.PRODUCT_ID
WHERE (C.CUSTOMER_STATUS_CODE = 'ACTIVE') AND (B.CYCLE_END_DATE >= GETDATE()) AND (A.ORDER_STATUS_CODE = 'A') AND
(B.LINE_STATUS_CODE = 'A') AND (B.FULFILL_STATUS_CODE IN ('A', 'G')) AND (MP.LEVEL1 IN ('NATIONAL'))
GROUP BY C.USR_HIGHEST_DEGREE

My problem here it , query gives error. I would appreciate if someone can give me any idea how to go about this. I tried calculating % outside sql but still doesn't work.

Any help would be appreciate.

learnasp:

My problem here it , query gives error

Please tell us the exact error.

|||

Hi,

Thanks for your reply. The error i get is

"Incorrect snytax near the keyword 'AS'.

But both queries run without any eroor separately. I run the nested select and main without nest select. Both give answers. Now I don't know what is problem.

Thanks

|||

I did not get any parsing error if I put a bracket after the multiply by 100 as shown below in red.

---

SELECT C.USR_HIGHEST_DEGREE,COUNT(DISTINCT C.MASTER_CUSTOMER_ID) AS [# MEM],(convert(numeric(5,2),COUNT(DISTINCT C.MASTER_CUSTOMER_ID))

/ (

SELECT convert(numeric(5,2),COUNT(CC.MASTER_CUSTOMER_ID))

FROM MBR_PRODUCT AS MPP WITH (NOLOCK) INNER JOIN
PRODUCT AS PP WITH (NOLOCK) ON MPP.PRODUCT_ID = PP.PRODUCT_ID INNER JOIN
ORDER_MASTER AS AA WITH (NOLOCK) INNER JOIN
CUSTOMER AS CC WITH (NOLOCK) ON AA.SHIP_MASTER_CUSTOMER_ID = CC.MASTER_CUSTOMER_ID INNER JOIN
ORDER_DETAIL AS BB WITH (NOLOCK) ON AA.ORDER_NO = BB.ORDER_NO ON MPP.PRODUCT_ID = BB.PRODUCT_ID
WHERE (CC.CUSTOMER_STATUS_CODE = 'ACTIVE') AND (BB.CYCLE_END_DATE >= GETDATE()) AND (AA.ORDER_STATUS_CODE = 'A') AND
(BB.LINE_STATUS_CODE = 'A') AND (BB.FULFILL_STATUS_CODE IN ('A', 'G')) AND (MPP.LEVEL1 IN ('NATIONAL'))

)*100)AS [%]

FROM MBR_PRODUCT AS MP WITH (NOLOCK) INNER JOIN
PRODUCT AS P WITH (NOLOCK) ON MP.PRODUCT_ID = P.PRODUCT_ID INNER JOIN
ORDER_MASTER AS A WITH (NOLOCK) INNER JOIN
CUSTOMER AS C WITH (NOLOCK) ON A.SHIP_MASTER_CUSTOMER_ID = C.MASTER_CUSTOMER_ID INNER JOIN
ORDER_DETAIL AS B WITH (NOLOCK) ON A.ORDER_NO = B.ORDER_NO ON MP.PRODUCT_ID = B.PRODUCT_ID
WHERE (C.CUSTOMER_STATUS_CODE = 'ACTIVE') AND (B.CYCLE_END_DATE >= GETDATE()) AND (A.ORDER_STATUS_CODE = 'A') AND
(B.LINE_STATUS_CODE = 'A') AND (B.FULFILL_STATUS_CODE IN ('A', 'G')) AND (MP.LEVEL1 IN ('NATIONAL'))
GROUP BY C.USR_HIGHEST_DEGREE