Analysis :: Find Date Ranges On Members And SUM
Jul 24, 2015
I've got two measure groups with a dimension (Dimension A) that is related to one measure group but not the other. I want to find the date ranges of the members of an attribute in Dimension A in Measure Group 1 and apply that to Measure Group 2 in a calculation. This way I can find the sum of a measure from Measure Group A that falls within the time periods of the attributes in Measure Group B. Part of my MDX for this new calculated member starts like this, but doesn't work.
WITH MEMBER [Measures].[New Measure] AS
IIF( ISEMPTY ([Measures].[Measure 1]), NULL,
SUM(([Date].[Hour].[Hour], [Dimension A].[Attribute].[Attribute]), [Measures].[Measure 2]))
View 3 Replies
ADVERTISEMENT
Jul 7, 2015
We have a date dimension which spans till 2099 and there are future projection numbers (under measures). I want to limit the data for Future projections only to 5 years from today by default. Is there a way to do this with in the cube. I understand that this can be done using MDX but since we use excel to view the data from the cube it needs to be controlled with in the cube.
View 4 Replies
View Related
Aug 31, 2004
Hi,
I have a Time Dimension which allows me to select a specific YEAR, or YEAR & QUARTER or YEAR & QUARTER & MONTH, or YEAR & QUARTER & MONTH & DAY.
Is there any way that I can have a range of dates?
Is it possible to have 2 time Dimensions for example which did the following:
a start: Year|Month for example
(>= Year|Month )
.......and......
an end: Year|Month
(<= Year|Month )
Thus I would be able to select a range of dates/months.
Do you know if this is possible to write this inot the dimension?
Thanks,
David
View 3 Replies
View Related
Jun 4, 2015
Problem setting is a geography dimension with multiple user defined hierarchies in SSAS 2008.
Ex.:
Hierarchy 1 (political territory): level 6 --> level 5 --> level 4 --> level 3 (state) --> level 2 --> level 1
Hierarchy 2 (sales territory): level 4 --> level 3 --> level 2 (sales region) --> level 1
...
Hierarchy 9
The relationship between state and sales region is n:1, i.e. one state belongs to exactly one sales region, and one sales region can consists of one or multiple states. Unfortunatly I can't define this attribute relationsship in the dimension because it would lead to a diamond-shaped relationsship without a user-defined-hierarchy to back it up. So far that isn't much of a problem, user don't drill down from sales region to state. But now I want to define a calculated member that multiplies a measure from the main measure group with another measure from a weighting factor measure group at the state level and above. The granularity attribute of the geography dimension in the dimension usage tab of the weighting factor measuregroup is the state.
So far what I've got is:
CREATE MEMBER Currentcube.Measures.[weighted measure state and above] AS NULL;
SCOPE (Measures.[weighted measure state and above],
Descendants(geography.[political territory].[all member],3,SELF_AND_BEFORE),
Descendants(geography.[salesterritory].[all member],2,SELF_AND_BEFORE),
... Descendants(geography.[hierarchy 9].[all member],1,SELF_AND_BEFORE)); this = sum(existing(geography.[political territory].state.members), measures.[main measure group measure] * measures.[weighting measure group measure]);END SCOPE;
This works from a functional point of view, but is rather slow when querying any other hierarchy than the political territory hierarchy, because SSAS first goes down from the state level to the key attribute of the geography dimension, and then aggregates from there to the sales region.In other words, I want SSAS to resolve the relationsship (which state belongs to which sales region) through the dimension, and not through the fact, and apply the calculation afterwards. Like some kind of currency conversion, but only from a certain level upwards.
View 5 Replies
View Related
Aug 20, 2015
I have sales from 2010 to today in my cube. my question is i want to see sales amount from 2010 to 2014 if i select 2014. or if I select 201405 ( may 2014), i want to see sales from 20100101 to 20140531. same with date. basically i want to to al the sales til the date/month /year selected. how can i achieve this?
View 2 Replies
View Related
Jul 10, 2015
Enterprise 2014 SQL Server - SSAS
I have a need to insert 50 - 100 new members each week.
Is there an SSIS procedure that will do this?
View 3 Replies
View Related
Sep 17, 2015
The Member_Key we can get by the method suggested in the post, but when we get the Member_Key, Id as Measures/members then performance is very slow.
Is there any way i can get the Member_key in a set in rows section.
Like
With SET [UniqueName] AS [Geography].[Country].CURRENTMEMBER.UNIQUENAME
Select Measures.Value on Columns,
UniqueName on Rows
From Cube
I want the value as just like
Value
Employees, UniqueName
View 2 Replies
View Related
Sep 27, 2013
I'm trying to do a currency conversion with an MDX statement in my Cube SSAS 2012.
Here is my script :
SCOPE( LEAVES([Entity]) );
SCOPE( LEAVES([Time]) );
SCOPE( LEAVES([Currency]));
SCOPE( [Account].[Account].[Total ACCOUNT].members)
THIS = ([Measures].[Value],[Currency].[Currency].[Local])*([Measures].[Value],[Account].[Account].[Fx Rate]);
END SCOPE;
END SCOPE;
END SCOPE;
END SCOPE;
The problem is I want to exclude frome the scope Currency my local Currency in order to make the conversion only if a currency (€,$, £) is selected. I tried the following syntax but it always return a "MDX script is not valid" :
SCOPE( LEAVES([Currency], Except (LEAVES([Currency],[Currency].[Currency].[Local]);SCOPE( [Measures].[Value] , Except ([Currency].[Currency].members,[Currency].[Currency].[Local]);SCOPE( [Measures].[Value] , Except (LEAVES([Currency],[Currency].[Currency].[Local]);SCOPE( LEAVES([Currency] - [Currency].[Currency].[Local]);
View 5 Replies
View Related
Dec 2, 2015
I've got a dimension built from a fact (whatever that's called?) ... it's a date interval field, i.e. 0-5 weeks, 6-10 weeks 11+ weeks. How do I sort these members in the respective order? Looks like this currently:
The problem lies in the fact that I don't have any secondary attributes to order it by, i.e. it's not a physical dimension where I can use a key for the 3 members. I was hoping I wouldn't need to create a separate dimension to get round this.
View 5 Replies
View Related
Jul 6, 2015
I have a date dimension with structure/hierarchy (year - month - wkofmonth - day) & measure count
This works fine when I insert wkofmonth in columns & count in values but when I add another measure it duplicates per week
Product CountWk1(jan) CountWk2(jan) CountWk3(jan) CountWk4(jan) SalesVolume
1 1 2 0 0 1000
Is there something I can do on the structure & do 4 calculated members pending how many weeks in a month, is there a simpler way where the next measure does not calculate by columns field.
If calculated member for each week, how do I do this, any example code?
View 2 Replies
View Related
Jul 8, 2015
I'm working with the statistical functions Stdev and Median with calculated members. The only way I can get the "correct" answer is if I have a dimension at the same granularity as the Fact table (Actually it's a degenerate dimension of the FACT table itself). Otherwise it seems that the measure I'm using with Stdev returns results that are so wildly high, I think it must be acting on the SUM of the measure; because the measure itself is a Summed one. When I try to use the coordinates in the Stdev function, it seems like it is using the wrong set of data points :
stdev( ( [Date].[Date].[Date].members, [Parameter].[Parameter].[Parameter].members ), [Measures].[Value]) returns answers in the thousands when it should be more like 2.5
When used with a query, there would only be a single date member and a specific parameter member. The total number of fact records is between 200 and 500 with values that range between 0 and 150. This is the version that gives me answers that resemble the total sum of the [Measures].[Value].
If I add the dimension that is essentially a row number from the fact table, it gives the right answer (slowly, but that will be a different post ....
stdev( ( [Date].[Date].[Date].members, [Parameter].[Parameter].[Parameter].members, [FACTTable].[FACTTable].[KeyField].members ), [Measures].[Value])
View 4 Replies
View Related
Jun 29, 2015
I am currently creating calculations for weekly and fortnightly ranges by day.
Is it better to use LastPeriods or Tail? For example, the last 14 days could be:
LastPeriods(14,
StrToMember
(
"[Date].[Year Week Day].[Date].&[" + FORMAT(Now(),"yyyy-MM-ddT00:00:00")
+"]"
)
))
Tail(
Filter(
{[Date].[Year Week Day].[Day]},
([Date].[Year Week Day].CURRENTMEMBER, [Measures].[Test Measure]) > 0
), 14
)
What is the advantages or disadvantages?
View 3 Replies
View Related
Mar 2, 2006
Do anybody know if Analysis Services 2005 allows cashing (storing) calculated members ?
There is a cube in Analysis Services 2000 with a calculated member based on some running sums. The performance of MDXs is too slow because running summs are being recalculated for each member of time dimension on each call.
I think that caching calculated members might help me and hope that this feature exists in Analysis Services 2005 .
Thanks!
View 1 Replies
View Related
Jun 9, 2015
I am using a "Client" dimension that includes a "Holding - Client" hierarchy. I have to make sure, that only the appropriate roles may access appropriate members from this dimension, but I only have the information which role may access which ClientID - I do not have the information which HoldingID should be accessible. Also, "Client" is used as the key column of the dimension with "ClientID" and "HoldingID" as key columns. The hierarchy is strict, no client may belong to multiple holdings.
I cannot seem to find the right MDX for the allowed member set. My MDX expression would need to look like this:
[Client].[Holding - Client].[Client].&[*]&[123]
In this example I want to give access for client &123, no matter the holding, so &1&123 and &2&123 would be allowed.
Is this doable?
View 4 Replies
View Related
Aug 17, 2015
Our SSAS integration didn't initially use attribute relationships.Now that our system has been running for a few years and we have bigger databases, we think we need to add them to improve performance. So we're in the process of adding them but we found out that, when attribute relationships are added, the full unique name of our members all go from something like:
[DIM].[HIERARCHY].[LEVEL].&[GRANDPARENT].&[PARENT].&[MEMBER]
to something like:
[DIM].[HIERARCHY].[LEVEL].&[MEMBER]
It looks nice and SSAS will accept the longer names fine but it will return the short ones in response to 'discovery' requests and in the XMLA response of MDX queries. This is causing problems in our low level XMLA-based modules that assume the long names in and out. is there any clean way to use attribute relationships and still have SSAS generate the long member names. We fiddled with the various documented dim/attribute properties but to no avail. It also appears that some switches are obsolete.
View 6 Replies
View Related
May 25, 2008
Find Time Ranges
I have a DateTime field, I need to find out how many records are in 8am-11am, 12pm-5pm, 6pm-7am regardless of date. How can I do this?
View 4 Replies
View Related
May 25, 2008
Find Time Ranges
I have a DateTime field, I need to find out how many records are in 8am-11am, 12pm-5pm, 6pm-7am regardless of date. How can I do this?
View 3 Replies
View Related
Mar 20, 2015
I am trying to find a beginning date from multiple date ranges, for example:
RowNumberidBegin dtEnd Dt
107933192014-09-022015-06-30
207933192013-09-032014-09-01
307933192012-09-042013-09-02
407933192011-09-062012-09-03
For this id: 0793319, my beginning date is 2011-09-06
108203492014-09-022015-06-30
208203492013-09-032014-09-01
308203492012-09-042013-09-02
408203492011-12-122012-07-03--not a continuous date range
For this id: 0793319, my beginning date is 2012-09-04
108203492014-09-022015-06-30
For this id: 0820349, my beginning date is 2014-09-02
To find continuous date, you look at the beginning date in row 1 and end date in row 2, then if no break in dates, row 2 beginning date to row 3 end date, if no break continue until last date There could multiple dates up to 12 which I have to check for "no break" in dates, if break, display beginning date of last continuous date.
View 9 Replies
View Related
Jun 2, 2015
I have a dimension report with an attribute reporttype which have different member let say A,B,C,D....etc.
I want to set more than one default members for dimension report.
I've read all about how dimensions can have only one default member, but I need to set more than 1.
View 2 Replies
View Related
May 25, 2008
I have a DateTime field, I need to find out how many records are in 8am-11am, 12pm-5pm, 6pm-7am regardless of date. How can I do this?
Kamran Shahid
Sr. Software Engineer(MCSD.Net)
www.netprosys.com
View 2 Replies
View Related
Nov 21, 2007
I am an amature SQL programmer and need some help.
I have a table of IP ranges that looks like:
NodeNameIP_StartIP_End
USA10.1.1.0 10.1.1.3
USA10.1.16.0 10.1.23.255
GER10.10.10.0 10.10.10.255
GER10.2.2.0 10.2.2.255
...
and a table with computer properties that has each computer's respective IP Address.
I want to query what NodeName each computer's IP belongs to.
Your help is appretiated.
View 2 Replies
View Related
Dec 19, 2013
Consider the following data
create table #sampletbl
(
Category varchar(100),
Numbers varchar(100),
Status varchar(100)
)
insert into #sampletbl values ('A','29710000001','0')
[code]....
if status = 0 then unused status = 1 then used
i need to find 10 continues unused numbers
This is the sample data. In live I'm having plenty of data like that . So quite complex.
note : In real world I need to find the 1000 continues number ranges.
View 8 Replies
View Related
Dec 17, 2006
Can someone please help me with this?
I need a query that will pull clients that made payments last year but not this year.
I need the query to use date parameters so I can select any date range for the past year and the current year.
I have listed the tables and fields that might be needed: I'm hoping to do this without temp tables.
Date range is based on the tblPaymentReceipts.PaymentDate
tblClients, ClientID
tblPayment, PmtID, ClientID, Paystartdate, Paygroup
tblPaymentReceipts, PmtRcptID, PmtID,CleintID,PaymentDate,PaymentAmount
View 6 Replies
View Related
Mar 18, 2004
I have two sets of dates to work with. One is an existing booking with a start and an end date. The other is a new booking with a start and an end date. I want to compare them and calculate how much overlap there is. If the overlap is over a certain amount (say 4 days), then I want to flag the user.
Is there any thing I can use in terms of a SQL query to assist in this comparison? I'm relatively new to SQL so I'm not entirely sure what functions and keywords are available to me to make this comparison.
View 3 Replies
View Related
Oct 25, 2005
I've gone cold here. Dunno if I've had too little coffee - as I'm currently drinking some seriously wicked green tea - or whether my brain has locked down from yesterdays "bad eggs for lunch" experience.
Anyway... I have database with a customer, for each customer is a related history table with assigned consultant.
The assigned consultant table has information on consultant id, name, the start date of his assignment and the end date.
I need to find all customers that currently have (or have had) two or more consultants actively assigned. In other words, I need to see if the start/end times overlap.
At my current state, I'm just done.. i can't maintain the perspective... how do I do this?
View 5 Replies
View Related
May 22, 2008
Hey,
I woudl like to take an age range (Say 22-27) and determine the min and max year, and then take those two DateTime Year values and do a SELECT from a column titled "Birthday Year", which is a an int value like '1984'. Could someone give me a hand with this?
/* Value 1: 1978
Value 2: 1988
*/
SELECT(???) FROM User WHERE ['BirthdayYear'] <> (?Age Function?)
GO
Thanks.
View 3 Replies
View Related
Apr 16, 2015
I have a set of MS SQL reports, that need to always run on a certain day of the month. Generally the 20th. If the report was to run few days before the 20th, say on the 10th, I wish to retrieve those days between the 20th from the previous month, till the current date.
e.g: '2015-4-10' should only return 20 days worth of data.
I have tried the following query:
SELECT
DATEADD(D, 1, MAX(CAST(DateTimeStamp AS DATE))) As EndDate,
MIN(CAST(DATEFROMPARTS(DATEPART(YEAR, DateTimeStamp),DATEPART(MONTH,
(SELECT CASE WHEN DATEDIFF(DAY,DATEPART(DAY, GETDATE()),28) <0 THEN (SELECT DATEPART(MONTH, GETDATE()))
ELSE (SELECT DATEPART(MONTH, GETDATE()) -1) END AS Date)),28)AS DATE)) AS StartOfMonth
FROM
tbLogTimeValues
WHERE
DATEPART(YEAR, DateTimeStamp) = DATEPART(YEAR, DATEADD(M, -1, GETDATE()))
Which parses ok and managed to test all individual queries, however, as a whole, I get the following error message "Cannot perform an aggregate function on an expression containing an aggregate or a subquery."
View 5 Replies
View Related
Jul 23, 2005
Hello,I am importing data that lists rates for particular coverages for aparticular period of time. Unfortunately, the data source isn't veryclean. I've come up with some rules that I think will work to clean thedata, but I'm having trouble putting those rules into efficient SQL.The table that I'm dealing with has just under 9M rows and I may needto use similar logic on an even larger table, so I'd like somethingthat can be made efficient to some degree using indexes if necessary.Here is some sample (simplified) code:CREATE TABLE Coverage_Rates (rate_id INT IDENTITY NOT NULL,coverage_id INT NOT NULL,start_date SMALLDATETIME NOT NULL,end_date SMALLDATETIME NOT NULL,rate MONEY NOT NULL )GOINSERT INTO Coverage_Rates VALUES (1, '2004-01-01', '2004-06-01',40.00)INSERT INTO Coverage_Rates VALUES (1, '2004-03-01', '2004-08-01',20.00)INSERT INTO Coverage_Rates VALUES (1, '2004-06-01', '2004-08-01',30.00)INSERT INTO Coverage_Rates VALUES (2, '2004-01-01', '9999-12-31',90.00)INSERT INTO Coverage_Rates VALUES (2, '2004-03-01', '2004-08-01',20.00)INSERT INTO Coverage_Rates VALUES (2, '2004-08-01', '2004-08-01',30.00)GOThe rule is basically this... for any given period of time, for aparticular coverage, always use the coverage with the highest rate. So,given the rows above, I would want the results to be:coverage_id start_dt end_dt rate----------- ---------- ---------- --------1 2004-01-01 2004-06-01 40.001 2004-06-01 2004-08-01 30.002 2004-01-01 9999-12-31 90.00There can be any combination of start and end dates in the source, butin my final results I would like to be able to have only one distinctrow for any given time and coverage ID. So, given any date @my_date,SELECT coverage_id, COUNT(*)FROM <results>WHERE @my_date >= start_dtAND @my_date < end_dtGROUP BY coverage_idHAVING COUNT(*) > 1the above query should return 0 rows.Thanks for any help!-Tom.
View 9 Replies
View Related
Dec 11, 2007
Hello Gang,
I have a strange problem that I haven't dealt with before.
I need to execute a piece of code based on date ranges. If the date range is:
Scenario 1:between 02/28 (Feb 28) and 07/31 (July 31) do x
-----------------------------------------------------------
Scenario 2:between 08/01 (Aug 1) and 01/31 (Jan 31) do y
I am trying to automate a report. The report is supposed to generate a result that will differ based on the date ranges going into the future. E.g.
[1]. If the run date of the report is between '2/1/20xx' and '7/31/20xx' display <ABC> or
[2]. If the run date of the report is between '8/1/20xx' and '1/31/20xx' display <PQR>
In example # 2. I am moving from one year to the next (July to Dec and the one extra month of Jan). So for example, if the guy runs the report between August of 2008 and January of 2009, display <PQR>.
How do I achieve both # 1 & 2 above in a code? Does this explain better.
Joshi
View 1 Replies
View Related
Mar 15, 2007
I'm currently using Reporting Services for SQL Server 2005. I have been able to setup, and configure the Report Manager interface, as well as generate reports via the Report Builder. What I have been unable to do is allow the user to dynamically set the date range that my SQL query will use. Can someone suggest / is it even possible wihtout using a custom web interface?
View 1 Replies
View Related
Jun 2, 2006
We have some seasonal products that we would like to turn on and off automatically on our website. To accomplish this, we've added StartDate and EndDate fields to the product table. See simplified table:
--------------Simple Table:--------------ID int identity (PK)Value varchar(50)StartDate datetimeEndDate datetime
Normally I could just do something like SELECT ID, Value FROM PRODUCTS WHERE StartDate <= @MyDate AND EndDate >= @MyDate
The catch is that we want to ignore the year part of the dates so that we don't have to go back through and update all the records every year. I'm pretty sure I've done this in the past, but I'm having a brain fart right now and can't remember how. One of the issues is that the start month could be > the end month (e.g. - October 15 - January 1). Using code in the application I could work around this (see below), but I would like to handle this in SQL if possible so I don't have to return more records than are needed and weed out the bad records.
'dtStart and dtEnd are Date Objects, StartDate and EndDate are strings containing the MM/DD partIf dtStart.CompareTo(dtEnd) > 0 And dtStart.CompareTo(myDate) < 0 Then dtEnd = Date.Parse(EndDate & "/" & (myDate.Year + 1))ElseIf dtStart.CompareTo(dtEnd) > 0 And dtStart.CompareTo(objDate) > 0 Then dtStart = Date.Parse(StartDate & "/" & (myDate.Year - 1))End If
I appreciate any help you can provide.
Thanks,Sam
View 1 Replies
View Related
Apr 23, 2008
Hello, I am trying to automate our FTE calculations, and I need to be able to determine the total days employed for a given employee for a given period of time. I have the date ranges they worked, but am not sure how to total those based on the required period, For example
empployee|Start Date|End Date
1|1/1/2005|3/1/2006
1| 4/15/2006| 1/1/2008
How do I total the number of days employed for this employee between 2/1/2006 and 2/1/2007?
Thanks in advance for any advice?
View 11 Replies
View Related
May 31, 2008
I have a table with date like this.
InstId--Date----Readings
--1---10/12/2008--10
--1---11/12/2008--10
--1---12/12/2008--10
--1---13/12/2008--9
--1---14/12/2008--10
--1---15/12/2008--10
--1---16/12/2008--10
--1---17/12/2008--10
--2---05/03/2008--8
--2---06/03/2008--6
--2---07/03/2008--8
--2---08/03/2008--8
--2---09/03/2008--8
--2---20/03/2008--8
Guys I want to get the date ranges instrument wise for which the instrument readings are constant.
For example for instrument 1 the readings are constant i.e 10 from 10/12/2008
till 12/12/2008 & then again it is constant from 14/12/2008 till 17/12/2008.
Same goes for instrument id 2.It is constant from 07/03/2008 till 20/03/2008.
I need to get the output like this.
StartDate EndDate Readings
10/12/2008 12/12/2008 10
14/12/2008 17/12/2008 10
17/03/2008 20/03/2008 8
Thanks for any help.
View 10 Replies
View Related
