Analysis :: MDX - Aggregate Measure For Specific Time Range / Filtering And Print Only Current Month
Aug 6, 2015
At the following MDX code , I want to get the aggregate of measure only for members that are also in the specified last time (like in examp 01/06/2015) . I tried existing and exists, but without any lack.
WITH MEMBER A AS
(b)+(C)
MEMBER [Measures].[Aggregate] AS
Aggregate(DAYTIME].[Month].&[2013-01-01T00:00:00]:[DAYTIME].[Month].&[2015-06-01T00:00:00],
([Measures].[D])
[Code] ....
View 13 Replies
ADVERTISEMENT
May 13, 2015
In my cube there are two measures which are used in different calculations.Now I'm need to show in report if there any months in data when both or even another one of the measures is not updated (value = 0 or NULL).
how should I create the calculated measure for that?
I have tried in mgmt studio to plan this but I'm in a loop of errors.
View 4 Replies
View Related
Jun 19, 2007
Im trying to get the top 10 sales people by date range
my cutdown select statement for my dataset is as follows:
SELECT TOP 20 SaleDate,Consultant, State, Sum(Value) As Winbacks, UserID
FROM dbo.tbl_kpi_sales
GROUP BY Consultant, State, SaleDate, UserID
HAVING (SaleDate)>= @Param_StartDate and (sales.SaleDate)<= @Param_EndDate) AND State IN (@Param_State)
ORDER BY Winbacks Desc;
The problem im getting is that when a date range is entered by the user, the same representative might appear in the top 20 list afew times as they made the top sale for different days, I am wanting the select statement to add up the total sales for that representative for that date range and return him as a single entry...Because the sale date is in the select statement the group by clause separates these records, I cannot take the sale date out off the select because i need it to determine the date parameter for the reports
for example for date range - 1/3/2005 to 20/3/2005 get the top 20 reps Im currently getting
Name Value Date
Jim Brown 20 1/3/2005
Jim Brown 10 12/3/2005
Jim Brown 23 13/3/2005
Tom 10 12/3/2005
etc...till top 20
I am wanting the following output
Name Value
Jim Brown 53
Tom 10
Is there someway off doing this which still allows me to use a date range entered into report services
View 3 Replies
View Related
Jul 22, 2015
Developing a Retail cube using SSAS 2012. One of the dimension is DimCustomer with SCD type II. Each Customer can be a member or a non-member over a period of time. We have StartDt and EndDt to reflect the membership status.
eg:
Joe is a member between 06-01-2014 and 31-08-2014
Joe is a non-member between 09-01-2014 and 01-31-2015
Joe is a member between 02-01-2015 and 04-30-2015
Joe is a non-member between 05-01-2015 and 12-31-9999
Without adding fact row of Joe for each day to reflect the membership status, I want to provide the ability to measure "Active Customers Count" on a given date. There are 2 million customers in the DimCustomer Table.
View 2 Replies
View Related
May 14, 2015
One of my models has order data, cost per order/invoice ID and then dimensions on Fiscal Year, category, etc...the usual.
A user wanted to search it for an exact order amount. (They knew for example that one of our accounts was not balancing by single order worth $746.13 and assumed it must be an order that was placed but never marked shipped that slipped through the cracks).
Now, in the model I have "order amount" as a field and then a measure that sums that.
I could expose that "order amount" field as a label and let them filter on it in Excel (and that works).
However, I haven't had any luck filtering on the actual measure "Total Order Amount". Such as OrderID-> View Filter -> "Total Order Amount" equals 746.13.
I assume this is due to a few things:
Measure calculates at different levels so filtering on a measure is difficult as you would have to place all the "slicers" and set them first before the measure would "exist" at a level where it could be $746.13. Orders by year would have $746.13 as part of it's year sum, but wouldn't exist as a stand alone line item orders by year 2015 might be 2 million.
Orders by category might exist at 500,000, 8,000, 15,146.36, etc... but not $746.13.
So I would need OrderID on there as a column so the measure could return at the value of $746.13 for one row for it to match the filter?
Basically:
1. Why it can't really filter on a measure?
2. Is there a better way to accomplish this other than exposing the actual column in the fact table "order amount" as it feels like that could cause all kinds of confusion if other users try to slice/filter on that not realizing exactly what it is meant to be?
View 3 Replies
View Related
Jul 21, 2015
I can't seem to upload images so here's the link: [URL] ....
I need to get the last value of # Orders week avg in the current context (the highlighted value). The calendar dimension here is a week level one so I don't think I can use LASTNONBLANK. The measure is from the facts_sales table which is linked to a calendar dimension via a date key.
View 8 Replies
View Related
Apr 20, 2015
I am trying to get the last 60 days before a month starts. I have a set that is returned from the query below :
SELECT
non empty
[Measures].[TRANSACTIONS Count] on 0,
non empty ([TRANSACTIONS].[Days].[Days],
[TRANSACTIONS].[Transaction Month].[Transaction Month]) on 1 from [cube]
I can get the cummulative count of last 60 days before month 2 by hardcoding the day of transaction of start of month like below :
WITH MEMBER
[Measures].[Cumm Account Count]
AS
(
AGGREGATE( [TRANSACTIONS].[Days].CurrentMember:NULL ,[Measures].[TRANSACTIONS Count])
)
SELECT
non empty [Measures].[Cumm Account Count] on 0,
non empty [TRANSACTIONS].[Days].&[3]:[TRANSACTIONS].[Days].&[3].lead(60) on 1 from [cube];
and for subsequent months by using the dates that the following month starts. How can I achieve the above result without having to use the day numbers, I tried to use the tail function (to get the months and star date) but it wont work because the range function accepts members only...
View 4 Replies
View Related
Sep 30, 2015
I need to create a set so that when a user selects a month in filter (say 201506) then it should give me a list of months from 201406 to 201506. Any appropriate MDX query.
View 7 Replies
View Related
Dec 26, 2013
I come in to work 6.30am, and need to audit what happened from 5pm when I left to 6.30am this morning. I have used code to search 13.5 hours back from any given time:
SELECT * FROM TRANSACTION_HISTORY
WHERE TRANSACTION_HISTORY.ACTIVITY_DATE_TIME > (SELECT DATEADD(hour,-13.5,(SELECT MAX (TRANSACTION_HISTORY.ACTIVITY_DATE_TIME) FROM TRANSACTION_HISTORY)))
Problem is if I run query later, I lose time from the start, eg. If I run query at 7am, I only get results from 5.30pm onwards. Rather than change criteria every day, I wanted to able to search from 6.30am of the current day, back to 5.30pm of the previous day.
View 3 Replies
View Related
May 21, 2015
I have a parent child dimension. a time dimension i have year.
Assume,
ParentID ChildID sales Year
171 171 10 2014
171 172 200 2014
171 173 300 2014
171 172 44 2015
if I pass 2014 and 2015 in sub select 171 data is not coming in result. i i pass only 2014 in sub select i get value of only 2014. if I pass 2015 in sub select i didn't get any value.
View 2 Replies
View Related
Jun 1, 2015
Need to display current time in below format:
06/01/2015 at 7:00 a PST
(Date at time TimeZone)
View 2 Replies
View Related
Aug 27, 2015
I have the following code block
CREATE TABLE #tbl_1 (event_time DATETIME2, SID INT ,NAME VARCHAR(20) )
INSERT INTO #tbl_1 VALUES ('2015-08-27 13:47:24.123','150','abc')
INSERT INTO #tbl_1 VALUES ('2015-09-27 13:47:24.123','149','acb')
INSERT INTO #tbl_1 VALUES ('2015-10-27 13:47:24.123','148','cba')
CREATE TABLE #tbl_2 (event_time DATETIME2, SID INT ,NAME VARCHAR(20) )
INSERT INTO #tbl_2
SELECT * FROM #tbl_1 where ? SELECT * FROM #tbl_2
My requirement is to insert values into #tbl2 that are in current month which are event_time values '2015-08-27'
View 4 Replies
View Related
Jan 27, 2009
Our client wants to report on their trade volume for last year as compared to the current quarter. For simplicity let's pretend they have a report where they have a two key measures:
[Trade Volume - Tons]
[Trade Volume - Tons MTD]
[Trade Volume - Tons]
is based on outlook - that is, for any period we are reporting on the trade volume will be reported as actuals that have been loaded up until the current period, and forecast for the current and future periods.
[Trade Volume - Tons MTD] is based only on actuals - that is for any period we are reporting on the trade volume will be reported as actuals that have been loaded up until and including the current period, and 0 for any future periods.
If Feb09 is our current period, and we are using quarter on the time dimension (where quarter 1=Jan09,Feb09,Mar09) and we have the following data:
Jan09 Trade Volume Actual: 100 Trade Volume Forecast: 150
Feb09 Trade Volume Actual: 50 Trade Volume Forecast: 200
March09 Trade Volume Actual: 75 Trade Volume Forecast: 225
Then
[Trade Volume - Tons]=100+200+225=525
[Trade Volume - Tons MTD]=100+50=150
This is a problem, because the comparison with their current results ([Trade Volume - Tons MTD]) with what they 'forecast' ([Trade Volume - Tons]) is not based on the same period of time - we are comparing the sum of two periods versus three periods.To solve this we changed the reporting period to be monthly granularity, and now select Jan09-Feb09 as our range (as opposed to having a quarter granularity and selecting Q1,2009 in the example above).
This works well and produces the expected results:
[Trade Volume - Tons]=100+200=350
[Trade Volume - Tons MTD]=100+50=150
However, this introduces a secondary problem: we are doing a prior year calculation on the Trade Volume also, so the users can compare how the actuals are comparing to the same period last year.To do this we use the following formula for the prior year calculation:
Prior Year Actuals=([Measures].[Trade Volume - Tons], ParallelPeriod([Time].[544 Hierarchy].[Period Year],1,[Time].[544 Hierarchy].currentmember))
The problem is as soon as we move from quarter granularity to (monthly granularity AND select more than one monthly period) the Prior Year Actuals calculation produces a an error "The MDX function CURRENTMEMBER failed because the coordinate for the 'Period Year' attribute contains a set".So, ParallelPeriod does not like it when currentmember is a range (Jan09,Feb09) rather than a single period (Jan09).
View 8 Replies
View Related
May 3, 2015
I want to filter a measure based on another measure (both are measures on the same FACT table).Distinct Number of Users HAVING.User Cost above 0.
I tried doing having but because it’s two measures and not a tuple with a dimension it writes an eror.My query is something like:
SELECT
[Measures].[Distinct Number of Users ]
having [Measures].[User Cost] >0
ON
COLUMNS,
[code]....
Please note, that I want it in a MDX query not needing to change the cube or DWH table.
View 4 Replies
View Related
Sep 22, 2015
I have 2 Dimensions in SSAS (see below end), I want to create a calculated member, named
This_Year_Billable_Objective_Count, with its formula = BillableLastYear*(100+ BillableObjective)/100.
The first factor, BillableLastYear is a number, aggregated (sum) from child units.
The second factor, BillableObjective is a percent number (for example 28 means 28%), it's not aggregate. It's an dependent value for each unit.
How can I calculate This_Year_Billable_Objective_Count for each unit?
\ able 1
SELECT [UnitKey]
,[UnitID]
,[UnitName]
,[Unit2Name]
,[Unit3Name]
,[Unit4Name]
[Code] .....
View 6 Replies
View Related
Jul 20, 2005
I have a complex statement that is used in a SELECT statement. Afterall my calculations I need to do an additional filter on the results.Can I use an alias anywhere or is the AS clause just for columndisplay purposes?Instead of:SELECT column1,column2,some_complex_statement_using_SUM_AVG_and_a_few_CAS E_statementsAS TotalFROM table_nameHAVINGsome_complex_statement_using_SUM_AVG_and_a_few_CAS E_statements[color=blue]> 1[/color]Can I have:SELECT column1,column2,some_complex_statement_using_SUM_AVG_and_a_few_CAS E_statementsAS TotalFROM table_nameHAVINGTotal > 1As I understand it, the HAVING clause is used for filtering AFTERaggregate functions are calculated so the alias SHOULD be available.
View 1 Replies
View Related
Jul 17, 2015
I am very new to MDX. I have the sales amount measure and date dimension.
I need two measure sales value for the below condition. I am going to display this result in SSRS.
1. For between from date and todate
2. For last week range
View 2 Replies
View Related
Nov 12, 2015
Any example on how to output more than one measure in an MDX query. Lets say I want to display stdev(x) and average(x) (or any other two measures)?
View 7 Replies
View Related
Dec 2, 2005
My objective is to only get the records for Current USER's region and district. Please let me know
what I am missing....Thank you!!!
private void Page_Load(object sender, System.EventArgs e)
{
if (!IsPostBack)
{
bindGrid();
}
}
private void bindGrid()
{
@username=HttpContext.Current.User.Identity.Name;
SqlCommand getCommand = new SqlCommand();
getCommand.Connection=sqlConnection1;
getCommand.CommandText="SELECT DealerCode,DealerName,ClaimCount," +
"Total$,CliamDays FROM DealerList,pwausers,pwadlrs WHERE" +
"@username=user_name AND parts_reg=dmpreg AND parts_dist=dmpdist";
sqlConnection1.Open();
sqlDataAdapter1.Fill(dataSet1);
DataGrid1.DataSource=dataSet1;
DataGrid1.DataBind();
Cache["DealerListData"]=dataSet1;
sqlConnection1.Close();
}
View 2 Replies
View Related
Jun 25, 2015
The following measure returns Month Sales: (blank)
Month Sales:=TotalMTD(Sum([Sales Amount]),'Date'[Full Date])
I have a relationship between ResellerSales-OrderDateKey and Date-DateKey
The relationship works fine when I use it in Access
View 3 Replies
View Related
Aug 16, 2006
I am attempting to write a SQL query that retrieves info processed between two times (ie. 2:00 pm to 6:00 pm) during a date range (ie. 8/1/06 to 8/14/06)... I am new to SQL and am perplexed... I have referenced several texts, but have not found a solution. Even being pointed in the right direction would be greatly appreciated!!
View 6 Replies
View Related
Sep 14, 2006
Hello;
i want to get some results but only for one month. like that;
select * from News where PubDate = month(9)
i tried use between dates but i got errors because all of months aren't 30 or 31 days ... For example February is 28 days sometime.. So how can i get a month results...
thanks now for your helping ...
View 4 Replies
View Related
Sep 2, 2015
I am getting same value all over how to get unique values for each measures.
how to resolve it.
View 5 Replies
View Related
May 18, 2015
The data attached below is from a Fact table. When this data is browsed in the Cube the end user is only interested in value of Measure 1 when it is not equal to zero. Measure 1 is a base measure .how to suppress the value 0 for Measure 1 in the Cube.
DimesionKey1
DimensionKey2
DimensionKey3
Measure 1
Measure 2
Measure 3
[code]...
View 4 Replies
View Related
Jun 10, 2013
I'm trying to create a percentile rank function based on the standard WIKI version:
I've seen Brian Knight's article here, but that only deals with percentile.
Where I'm struggling is getting the count of members in a set using a measure, in the current context on the same hierarchy, as the filter expression. I'm using the comparative set as in belonging to the same geographical location, and therefore associating by another attribute.
So, cl as below:
MEMBER [Measures].[RegionPercentileCount] AS
Count(
Filter(
NonEmpty(
descendants(Ancestor(
[Supplier].[NameMap].CurrentMember,
[Supplier].[NameMap].[Region]),
[Supplier].[NameMap].[Supplier Id]),
[Measures].[ActiveMeasure])
, [Measures].[ActiveMeasure] <
([Supplier].[NameMap].CurrentMember,
[Measures].[ActiveMeasure])))
Using the same measure and context hierarchy is always going to be equal, and therefore the count is always zero. Its almost as if I need a nested context for the FILTER which allows me to use enumerate the set on the same hierarchy whilst maintaining the external reference.
I'm thinking that perhaps I'm going to have to create another hierarchy and use that as the filter set and reference through StrToMember or similar.
View 3 Replies
View Related
May 8, 2015
I have a dimension called as DIM1 which has list of all measures and has an attribute called as ATTRMDX Formula. The formula will be like
([DIM1].[ATTR Measure Code].&[M1],[Measures].[ATTR MEASURE VALUE])+([DIM1].[ATTR Measure Code].&[M2],[Measures].[ATTR MEASURE VALUE]).
I want to pass this formula to a calculated measure as given below -
MEMBER [Measures].[FormMeasure] as ([Measure].[ATTRMDX Formula].currentmember.MEMBERVALUE)
but I get the string value itself as output, but when I put the formula as a string in the calculated measure I obtain the value.
View 2 Replies
View Related
Sep 9, 2015
I am trying to count a set as a calculated measure, when this set is called directly in the row , it returns fast, but when i try to count the set as calculated measure(so i can slice with another dimension) the query keeps running forever.
The queries are below
select
{} on 0,
nonempty
(
{([Transaction].[RPC Count].&[1],[Transaction].[Account ID].[Account ID])}
,
{([Account].[PAYMENTSTATUS].&[0],[Account].[Account ID].[Account ID])}
) on 1
[Code] .....
View 5 Replies
View Related
Aug 3, 2015
I have built a fact table and few dimension views in Datamart with the aim of creating a Cube.
On the Fact table I have added a CASE Statement with the following threshold for Premium due amounts:
CASE WHEN....
'Due_0-1_Month'
'Due_1-2_Month'
'Due_2-3_Month'
'Due_Over_3_Months'
'Overdue_0-1_Month'
'Overdue_1-3_Month'
'Overdue_3-6_Month'
'Overdue_Over_6_Months'
...END
I then created a Dimension to link this to:
CREATE VIEW...
Select 'Due_0-1_Month' as Ageing_Threshold
union all
Select 'Due_1-2_Month'
union all
Select 'Due_2-3_Month'
[Code] ....
I was successful in processing the cube, however the problem is everytime I drag the dimension on the columns field in Pivot tables the Thresholds start to break up the other amounts that I have on display like Acquisition Costs, Tax amounts. I am only interested in showing the breakdown of Premium amount measure by the Threshold dimension.
somehow 'Hide' or 'prevent' the Threshold dimension from breaking down the other measures on the Pivot and only breakdown the amounts for Premium?
how I should structure my tables in SQL or any MDX queries to resolve this.
View 0 Replies
View Related
May 14, 2015
I have a wide fact table that I'm feeding to an SSAS cube. I was advised that splitting the measure group into two will improve performance when querying the cube.
I cannot find any documentation that supports this, in fact I get a blue curved line suggesting that I merge the measure groups since they have the same dimensionality and granularity.
I guess the best practice is what the blue line states, but without knowing the internals of SSAS I can undestand that a smaller measure group may be easier to handle, or create more specific aggregations for.
View 4 Replies
View Related
Jul 24, 2015
SSAS 2008 R2
Is it possible to filter out a measure only at the intersection of Two dimension members? I have a date dimension, a Hospital dimension and a wait time measure.
For Example, is it possible to filter out Wait time for Bayside Hospital for the Month of June 2015?
I want Wait time to continue to be displayed for all other months and roll up into the totals without the filtered value.
View 4 Replies
View Related
Sep 2, 2015
How to put these calculated measures under the folder "sales line". i want to see it under the drop down...
View 5 Replies
View Related
Nov 4, 2014
I'm trying to show measures from 2 measures groups in a drillthrough. Obviously, it's not possible with a standard drillthrough action, but I still hope that I can somehow achieve this with the ASSP GetCustomDrillthroughMDX function.
Speaking in AdventureWorks2008R2 terms: Imagine I have a pivot table with Product Categories in filter (say, filtered on Gloves) and "Internet Sales Amount" as measure. From context menu in Excel I can call the drillthrough action which shows me the individual sales records. I would like to show in drillthrough additionally "End of Day Rate" measure from "Exchange Rates" measure group.
One option would be to join FactInternetSales with FactCurrencyRate and make EndOfDayRate a physical measure in the "Internet Sales" measure group. This is a pretty huge overhead for my scenario and I'd like to avoid this.
Another one would be to call something completely external for a drillthrough (for example, a SRRS Report).
View 3 Replies
View Related
Sep 21, 2015
I am executing a CUBE which is having 9 measure groups, i need to know order of the execution of the measure groups.??
ex:
1. fsales
2.ftranssales
3.fitem
4.fstores..............etc...
I know, the dimension are executed first, but not aware of the order of execution of measure group.
View 4 Replies
View Related
