January 27, 2012 at 3:35 pm
I have a calculated Memeber that words fine when I have [ServerTime].[Fiscal Year - Fiscal Month - Fiscal Day].[Fiscal Month] in my Where clause but will not give me the correct result when I have [ServerTime].[Fiscal Year - Fiscal Month - Fiscal Day].[Fiscal Day]. Fiscal day only works when the actual day is within a period that had amounts to be added. My query is this:
WITH
MEMBER [Measures].[LastYearTotalQty] as
sum(parallelperiod([ServerTime].[Fiscal Year].[Fiscal Year],1,[ServerTime].[Fiscal Year].currentmember),[Measures].[QTYINV])
select
{
[Measures].[LastYearTotalQty]
} on 0, non empty
{
[InventItems].[INVENTTABLE].[INVENTTABLE] *
[Customers].[CUSTOMERS].[Level 02]
}
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME on 1
FROM [SalesCube]
where
( [InventItems].[ITEMID].&[F24],
[ServerTime].[Fiscal Year - Fiscal Month - Fiscal Day].[Fiscal Day].&[2012-05-01T00:00:00] )
This gives me the correct result since in May, 2011 there were some units sold, the only ones in 2011.
The following:
WITH
MEMBER [Measures].[LastYearTotalQty] as
sum(parallelperiod([ServerTime].[Fiscal Year].[Fiscal Year],1,[ServerTime].[Fiscal Year].currentmember),[Measures].[QTYINV])
select
{
[Measures].[LastYearTotalQty]
} on 0, non empty
{
[InventItems].[INVENTTABLE].[INVENTTABLE] *
[Customers].[CUSTOMERS].[Level 02]
}
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME on 1
FROM [SalesCube]
where
( [InventItems].[ITEMID].&[F24],
[ServerTime].[Fiscal Year - Fiscal Month - Fiscal Day].[Fiscal Day].&[2012-04-01T00:00:00] )
returns nothing even though it should since the activity is withing the year 2011.
if I change the time hierarchy to be:
[ServerTime].[Fiscal Year - Fiscal Month - Fiscal Day].[Fiscal Month].&[2012-04-01T00:00:00] it gives me the correct amount.
I have tried changing the calculated measure as so witht he exact same results:
sum(parallelperiod([ServerTime].[Fiscal Year - Fiscal Month - Fiscal Day].[Fiscal Year],1,[ServerTime].[Fiscal Year - Fiscal Month - Fiscal Day].parent ),[Measures].[QTYINV])
Can anyone point me in the right direction?
Don
January 27, 2012 at 4:17 pm
I think I found a solution to use:
sum(parallelperiod([ServerTime].[Fiscal Year - Fiscal Month - Fiscal Day].[Fiscal Year],1,[ServerTime].[Fiscal Year - Fiscal Month - Fiscal Day].parent.parent ),[Measures].[QTYINV])
In my calculated measure. basically telling me to give me the sum for the prior 1 grandparent of the date. Grandparent would be the fiscal year in this case and so far it seems to work.
Anyone else have a better idea?
Don
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply