September 24, 2014 at 12:49 am
Hi ALL,
I need help on one the requirements where i need to find the no of days for the Date period Selected . Let me brief the explanation for you
I have got a date Hierarchy with Year->Quarter->Month->Date
Now I need an output of No. of Days of that Date period selected like
Example
if he selects "Date" - > 1 Day
if he selects "Month (January) " -> 31 Days
"Month (February of 2014) " -> 28 days
"Month (February of 2012) " -> 29 days
if he selects "Quarter Q1 2014 " -> 90 Days
if he selects "Year 2013 " -> 365 Days
if he selects "Year 2014 " -> No of Days until the Day in the hierarchy
I hope i am clear , I need an MDX function that could help me out here . Hoping for some help 🙂
Thanks & Regards,
Guitar_Player.
September 24, 2014 at 2:32 am
Ok, to get a basic count of days in each member you can use a calculation like this:
MEMBER DayCount AS
COUNT(Descendants([i]your dimension name[/i].[i]your hierarchy name[/i].CurrentMember,4),INCLUDEEMPTY)
What that is doing is counting the fourth level of the hierarchy (in your case date) based on the current selected member.
Getting it to count up to the current date in cases where it is current year/quarter/month is trickier. Give the above a try as a starter for ten and I'll have a think.
September 24, 2014 at 3:50 am
Hi PB_BI,
some what i was able to solve the problem..with other technique...i have defined a new column and assigned the value as 1 to it and declared that as measure with sum .
So if i select the feb 2012 i am getting the expected output and the same for others too..:-D
September 24, 2014 at 3:53 am
Right...this is a bit of cludge but it performs ok and does the trick. This uses the AdventureWorks fiscal hierarchy so it is counting the 5th level (which is day). You'll need to modify it for your own hierarchy. See comments for what is going on........
MEMBER DayCount AS
COUNT(
EXCEPT(
Descendants([Date].[Fiscal].CurrentMember,5),
// This is the "EXCEPT" part. It's using a date range from today to some point in the far far future and excluding it.
// Note how I have had to build up the format of the date member using a strtomember, format and now functions - you'll have to do this to match
// your own format.
STRTOMEMBER('[Date].[Fiscal].[Date].&['+Format(Now(),'yyyyMMdd')+']')
:
STRTOMEMBER('[Date].[Fiscal].[Date].&[99991231]'))
,INCLUDEEMPTY)
I hope that helps 🙂
September 24, 2014 at 3:54 am
Guitar_player (9/24/2014)
Hi PB_BI,some what i was able to solve the problem..with other technique...i have defined a new column and assigned the value as 1 to it and declared that as measure with sum .
So if i select the feb 2012 i am getting the expected output and the same for others too..:-D
....or you could do that which is much easier 😀
September 24, 2014 at 4:31 am
MEMBER DayCount ASCOUNT(
EXCEPT(
Descendants([Date].[Fiscal].CurrentMember,5),
STRTOMEMBER('[Date].[Fiscal].[Date].&['+Format(Now(),'yyyyMMdd')+']')
:
STRTOMEMBER('[Date].[Fiscal].[Date].&[99991231]'))
,INCLUDEEMPTY)
I hope that helps 🙂
Thank you 🙂 !! I will try this in my freetime ...Now i need another help .I am trying to find the Distinct count of Measure and so i have declared a Measure as Distinct count as [MeasureCount]
Now my reqirement is something like whatever i select (i.e) Year or Quarter or Month or Date , the related distinct count of each Date should get as the output . Let me explain it briefly
If i select a month , in that month for each day what is the distinct count of Measure
If i select a Quarter , in that quarter for each day what is the distinct count of Measure
If i select a Year , in that year for each day what is the distinct count of Measure
and i need to sum the measures of all the dates for the respective Date hierarchy selected . I hope i have explained in an understandable format
Please let me know if you require more detail
September 24, 2014 at 4:49 am
Guitar_player (9/24/2014)
Thank you 🙂 !! I will try this in my freetime ...Now i need another help .I am trying to find the Distinct count of Measure and so i have declared a Measure as Distinct count as [MeasureCount]
Now my reqirement is something like whatever i select (i.e) Year or Quarter or Month or Date , the related distinct count of each Date should get as the output . Let me explain it briefly
If i select a month , in that month for each day what is the distinct count of Measure
If i select a Quarter , in that quarter for each day what is the distinct count of Measure
If i select a Year , in that year for each day what is the distinct count of Measure
and i need to sum the measures of all the dates for the respective Date hierarchy selected . I hope i have explained in an understandable format
Please let me know if you require more detail
You can set up a record count of a fact table in measure group, any linked dimension used in a query will then count these facts distinctly. What specifically are you looking at counting distinctly? By which I mean what qualifies the count as "distinct" as opposed to just a regulat count?
September 24, 2014 at 4:52 am
Our Fact is having many records and even the duplicates of it too.. so if i consider counting distinct on day basis which is correct but if i consider the counting of distinct in monthly or quarterly basis or yearly basis my output is wrong . So what i have to do is for each day what is the distinct count i need to take and for the selected Date period i need to sum up all values of all the dates which comes in the selected date period .
September 24, 2014 at 5:53 am
Ok, I may be wrong but it seems like you need to do either one of two things:
1) If you have duplicates in your fact table, eliminate them. You can do this by adjusting your ETL or by using a view or a named query in your DSV.
2) If 1 isn't possible, look at the distinct count function (http://msdn.microsoft.com/en-us/library/ms145519.aspx)
I really don't know what exactly you are getting at to be honest though.
September 24, 2014 at 6:26 am
Hmm...As we have built everything in one Fact , we got this duplicate value issue and we cant go back at our current scenario and i have checked the link provided by you too...that one is also not quite helpful .
Mainly i just need the output as Dates on X Axis and one MEasure on Y axis which i am going to sum the count
and the parameter selected might be any of the Date hierarchy , i need to get those dates on X axis and the Measure on Y axis
September 24, 2014 at 6:53 am
Guitar_player (9/24/2014)
Hmm...As we have built everything in one Fact , we got this duplicate value issue and we cant go back at our current scenario and i have checked the link provided by you too...that one is also not quite helpful .Mainly i just need the output as Dates on X Axis and one MEasure on Y axis which i am going to sum the count
and the parameter selected might be any of the Date hierarchy , i need to get those dates on X axis and the Measure on Y axis
So when you mention duplicates you just mean that you might have, for example, n Sales that occurred against dateid "1" and not that the entire row is duplicated? If so then that's fine. I still don't know what you are getting at....Putting it simply, if you have a measure group with a count against it for record number then by slicing it with a related dimension then you will get a summed value to whatever level you are looking at. For example in AdventureWorks....
SELECT
{[Measures].[Internet Order Count]} on 0
,[Date].[Fiscal].[Fiscal Year] on 1
from [Adventure Works]
...this will give you a count of internet orders that is summed up to the Fiscal Year level of the hierarchy. If you are saying you have more than one factual piece of data in your fact table (e.g. Sale Amount and Cost Amount) then you can create separate measures for them.
September 24, 2014 at 10:59 pm
I agree to this, normally when we built a Dimensional modelling then automatically through slicing and dicing we will get the required data o/p based upon their hierarchy levels..
Consider No. of Rooms occupied in a Hotel , then per day basis if you see the count you get is good result , if your are doing this through quarter or monthly or yearly , since the rooms will be same and if you take the distinct count of it ? then the answer is not the same ? Rite ?
So my scenario is also same kind , i am calculating the Average of rooms occupied per Date selection it might be year , month or date .Since everything was kept in a Fact table when i am taking the distinct count of beds per day basis i am getting correct result but when i am considering the yearly or monthly basis i am not getting correct result. So what i thought of achieving this is if the user selects a Month then per each day of the month what is the distinct count of rooms and summing all the days of that month ! Similarly for the year and quarter too .
I hope i have given clear explanation of my current scenario .
September 26, 2014 at 3:43 am
So you just need a count of the occupied rooms over the course of months, quarters and years?
I still don't think you need to distinctly count anything. Why not have a separate fact for occupied rooms? Something simple like:
(FactRoomID INT IDENTITY,
Date_FK INT,
any other FKs)
Then just use a measure group count in the cube and you're done.
September 30, 2014 at 9:13 am
Here did you mean this needed a row for each date in the new fact table ?
Vineet D.
October 21, 2014 at 5:59 am
PB_BI (9/26/2014)
So you just need a count of the occupied rooms over the course of months, quarters and years?I still don't think you need to distinctly count anything. Why not have a separate fact for occupied rooms? Something simple like:
(FactRoomID INT IDENTITY,
Date_FK INT,
any other FKs)
Then just use a measure group count in the cube and you're done.
Sorry for the delay response..as i went on Long Holiday plan 🙂
Yes.. i think thats the only solution. Well i am thinking of creating a new table for that , but how can i join that Rooms Fact table with the main Fact table ?
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply