Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

adding columns Expand / Collapse
Author
Message
Posted Thursday, March 28, 2013 7:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, July 26, 2014 11:17 PM
Points: 49, Visits: 96
hi,

i have an mxd query that returns the data for yesterday (dynamic). what do i need to do to return the data from the day before yesterday in the second column and make a third column with an avg calculation of the first and second column.

here is my code.

with
member [Measures].[Max] As max({[Hour Dim].[D Hour].[All].children},[Measures].[Purchase - converted to USD])
member [Measures].[Min] As min({[Hour Dim].[D Hour].[All].children},[Measures].[Purchase - converted to USD])
member [Measures].[Avg] As avg({[Hour Dim].[D Hour].[All].children},[Measures].[Purchase - converted to USD])
member [Measures].[Void] As Aggregate(
CROSSJOIN({strToMember("[CHB Date].[Year - Month - Period - Date].[Date].&[" + Format(dateadd("d",now(),-1), "yyyy-MM-ddT00:00:00") +"]") },
{ [Chargebacks].[CHB_Tree].[CHB Type].&[IRS VOID] }), [Measures].[Purchase - converted to USD])
member [Measures].[CHB] As Aggregate(
CROSSJOIN({strToMember("[CHB Date].[Year - Month - Period - Date].[Date].&[" + Format(dateadd("d",now(),-1), "yyyy-MM-ddT00:00:00") +"]") },
{[Chargebacks].[CHB_Tree].[CHB Group].&[VOID],[Chargebacks].[CHB_Tree].[CHB Group].&[REFUND],[Chargebacks].[CHB_Tree].[CHB Group].&[ChargeBack]}), [Measures].[Purchase - converted to USD])
select
strToMember("[Time].[Year - Month - Period - Date].[Date].&[" + Format(dateadd("d",now(),-1), "yyyy-MM-ddT00:00:00") +"]") on columns,
{[Measures].[Purchase - converted to USD],
[Measures].[Purchases_Count],
[Measures].[Site Pay User],
[Measures].[Free_signups],
[Measures].[free to site pay user],
[Measures].[click to site pay user],
[Measures].[Click Count],
[Measures].[Guests Logins Count],
[Measures].[Guests Unique Logged in],
[Measures].[Guests Credit Spent],
[Measures].[Max],
[Measures].[Min],
[Measures].[Avg],
[Measures].[Void],
[Measures].[CHB] } on rows
Post #1436452
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse