getting sum of three months with the 3 months displayed separately

Question

getting sum of three months with the 3 months displayed separately

hemesh

Valued Member

Points: 59
More actions
November 9, 2006 at 9:45 am

#174848

hi,
i am new bie to mdx. i got stuck with writing a query.
my problem is like this,
jun | jul | aug |sum(jun jul aug )
aug will be a parameter
i want the query to return the values for three months with the sum of these months.
my query is like this:
with set aa AS
'crossjoin
(
{LastPeriods(3,[Date].[Year].&[2006].&[200611])},{[Measures].[Sales R TY]}
)'
member [Measures].[sum1] as
'sum(LastPeriods(3,[Date].[Year].&[2006].&[200611]),[Measures].[Sales R TY])'
select{aa}
on columns,
non empty DESCENDANTS([Site],1, SELF_AND_AFTER) on rows
from [ActuateReports]
i am not able to put both calculated values in the row
like select {aa,[Measures].[sum1])on row
plz give a solution for this problem

thanks,
hemesh

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

If you try this without crossjoining the measure and also mod your calculated member, it should work. Maybe something like....

with

member [Date].[sum1] as

'sum(LastPeriods(3,[Date].[Year].&[2006].&[200611]),[Measures].CURRENTMEMBER)'

select {LastPeriods(3,[Date].[Year].&[2006].&[200611]), [Date].sum1} on columns,

non empty DESCENDANTS([Site],1, SELF_AND_AFTER) on rows

from [ActuateReports]

WHERE [Measures].[Sales R TY]

Steve.

hemesh Valued Member Points: 59 More actions · Answer 2

hi steve,

thanks for ur reply.

But still my problem will not solve.The query which i posted yesterday is only a part my query, i need the same data for the [Measures].[Sales R LY],also the sum of twelve months, and sum of previous 12 months also.

So in that situation i believe we will not be able to put

WHERE [Measures].[Sales R TY] in the query . rite?

so any idea for this?

thanks,

hemesh

hemesh Valued Member Points: 59 More actions · Answer 3

hi steve,

my report layout will be like this:

Sales R TY Sales R LY

jun 05|jul05|aug05 |total 05| jun 04|jul04 |aug 04|total 04|

sum(12 months 05) |sum (12 months 04)

thanks

hemesh