Date dimension has to get the current date from system and then display thireen

[xyz]

Hi Gurus,

I have a date dimension, in the report from which I am using 'fiscal week' column as a prompt, which have week wise dates. But the fiscal week column has the data present for future years also. But my requirement is I have to only get 'fiscal weeks' from the current date to 13 weeks back data. The data in 'fiscal week' column is in mm/dd/yyyy format. Which I guess is in string format.

Say suppose today date in '10/21/2013', which is a current date then prompt has to display values like below
'10/21/2013'
'10/14/2013'
'10/07/2013'
'09/30/2013'
'09/23/2013'
'09/16/2013'
'09/09/2013'
'09/02/2013'
'08/26/2013'
'08/19/2013'
'08/12/2013'
'08/05/2013'
'07/29/2013'

Can someone please help me how to write the expression to get the above date's using DMR function, datasouce is SSAS cube.

Thanks & Regards,
xyz

[FabianGaussling]

Hi,

you need to create a filter like this:

to_date([Fiscal Year];'MM/DD/YYYY') between sysdate-7*13 and sysdate

You have to replace "Fiscal Year" by the name of your column. And the to_date function is needed to convert a string to date. And I have to add that this one works on an oracle database. I am not sure if it works on a microsoft as well.

Fabian

[xyz]

Thanks, Fabian for the reply.

I took the seperate query and added the dataitem, inside the data item, I added [Cube].[Dimension].[Hierarchy Year].[FiscalWeekDate], which I want to use it as a value prompt use value and display value. In filter I have used the below expression.

[Cube].[Dimension].[Hierarchy Year].[FiscalWeekDate] between (current_date) and (current_date*91)

But I am unable to do filtering and I am getting the error for the above expression.

QE-DEF-0459 CCLException
QE-DEF-0478 Invalid coercion from 'level' to 'Value' for [Cube].[Dimension].[Hierarchy Year].[FiscalWeekDate].

Please help me in fixing the issue, as I am new to Cubes and DMR functionality.

Thanks & Regards,
xyz

[CognosPaul]

In OLAP sources you have dimensions, hierarchies, levels, members, measures, and attributes. A member is basically a pointer to a specific data set. Each member has a list of attributes associated with it, such as a caption, business key, and more. You can see the list of attributes by expanding the level. Below the members folder, you should see a list of items. In SSAS attributes can be pretty much any data type, including Date and DateTime. In cases like this, I find it easier to create a numeric attribute with the value yyyymmdd.

I've written a fair amount about building reports on OLAP sources. It requires a different way of thinking than relational, but it is ultimately significantly easier to work with.

Right now it seems like you need to filter a query based on a static time range. The question than becomes, do you need to filter the entire query, or a specific point? For example, you may have a crosstab node that shows the selected time range, and another that shows the same range for the previous year. How are you showing the data? Are you showing the dates themselves, or aggregating them together?

In a relational source if you want to show the dates you would drag the date into the crosstab, and use a detail filter. In a dimensional query, you would create  a data item with the following expression:

filter([Cube].[Dimension].[Hierarchy Year].[FiscalWeekDate],[Cube].[Dimension].[Hierarchy Year].[FiscalWeekDate].[Date Attribute] between #timestampMask($current_timestamp,'yyyymmdd')# and #timestampMask(_add_days($current_timestamp,-91),'yyyymmdd')#)

If you want to show the set in the crosstab, you would drag it in. If you want to filter the entire query, you'd drag that into the slicer.

[lalitha.nov20]

Hi Paul,

I tried the expression provided by you . Modified according to the requirement.

filter([Labor].[Date].[Hierarchy - Fiscal Year].[Date],caption([Labor].[Date].[Hierarchy - Fiscal Year].[Date]) between #timestampMask($current_timestamp,'yyyy-mm-dd')# and #timestampMask(_add_days($current_timestamp,-91),'yyyy-mm-dd')#)

This gives me the below error:

       MSC-ERR-0051

      The query is not valid because the xsd:date type is not supported.


Thank you,
Lalitha

[xyz]

Thank you very much CognosPaul, you are awesome.

Actually my requirement is, I have date prompt which is coming from date dimension on fiscal week level, below is the level i am using [Cube].[Dimension].[Hierarchy Year].[FiscalWeekDate]. This prompt has data in mm/dd/yyyy format and have data for future years as well, which is value prompt not the static prompt. My requirement is I have to show only those date's in prompt, which starts from current date, which is nothing but today's date or system date to 13 weeks back date's in prompt, prompt is a single select drop down prompt. So I have planned to create a seperate query for the prompt page and then show only those date which starts from current date to 13 weeks back date's, so I want to create the filter and filter the entire query of prompt query.

I went and expanded the [Cube].[Dimension].[Hierarchy Year].[FiscalWeekDate] level below it, I can see
Members folder, which have all the fiscal week dates and Current Date Flag, Fiscal Month, Date-Fiscal-Week-Key, Member Description.

filter([Cube].[Dimension].[Hierarchy Year].[FiscalWeekDate],[Cube].[Dimension].[Hierarchy Year].[FiscalWeekDate].[Date Attribute] between #timestampMask($current_timestamp,'yyyymmdd')# and #timestampMask(_add_days($current_timestamp,-91),'yyyymmdd')#)

I used the above expression given by you and used in the slicer, in place of [Date Attribute], I used [Fiscal Month]
[Cube].[Dimension].[Hierarchy Year].[FiscalWeekDate].[Date Attribute]
[Cube].[Dimension].[Hierarchy Year].[FiscalWeekDate].[Fiscal Month]

When I tried to validate the expression, I am getting below error
QE-DEF-0459 CCLException
OP-ERR-0177 Invalid to compare values with different dataTypes

Can you please help me.

Thanks & Regards,
xyz

[CognosPaul]

If you have a current week flag, then it becomes significantly easier.

lastPeriods(-14,item(filter([Cube].[Dimension].[Hierarchy Year].[FiscalWeekDate],[Cube].[Dimension].[Hierarchy Year].[FiscalWeekDate].[Current Week Flag]=1) ,0))

That's assuming that the Current Week Flag is numeric 1. The filter returns a set, even if it's only one member. The item extracts a specific member from that set, in this case index 0, and the lastPeriods will retrieve the next 14 weeks (- goes into the future).

[xyz]

Thank you very much Paul.

The code you provided is working for me, but actually [Current Week Flag] have values like 'n','y' and null. I assumen data is junk in [Current Week Flag] column. We need to get the clarification on the same from DB Admin. Mean while

[Cube].[Dimension].[Hierarchy Year].[FiscalWeekDate] level have weeks data, which starts on every Saturday. Data in [FiscalWeekDate] will be 10/19/2013, 10/12/2013, 10/05/2013 etc for future weeks as well as past weeks.

Example: Say suppose today's date is 10/22/2013 then fiscal week for current week should be 10/19/2013. Similarly say suppose today's date is 10/16/2013 then fiscal week for current week should be 10/12/2013.

Consider today's date is 10/22/2013, so in this case fiscal week prompt should display date starting from 10/19/2013 to 13 weeks back date's like below

10/19/2013
10/12/2013
10/05/2013
09/28/2013
09/21/2013
09/14/2013
09/07/2013
08/31/2013
08/24/2013
08/17/2013
08/10/2013
08/03/2013
07/27/2013

Thanks & Regards,
Abdul

[lalitha.nov20]

Hi Paul,

I have similar issue, i need to display the previous week form the cube(no restriction on the count of week) from the current week. i used the below given expression by you

lastPeriods(-14,item(filter([Cube].[Dimension].[Hierarchy Year].[FiscalWeekDate],[Cube].[Dimension].[Hierarchy Year].[FiscalWeekDate].[Current Week Flag]=1) ,0))

but he clause is, if say today Nov 14 is thruday of the week and this does not complet my week, i need to display date from prevoius week .

Please let me know how do i go about it.
My time dimension is as follow.
year
quarter
month
week
date
date also have attributes like
day fo the week
week fo the year.

[CognosPaul]

So you both have the same problem, it's possible to fix but a bit of a pain.

The solution I've found is to create a parameter map in Framework that returns the day of week (since there's no other way to get it in a dimensional query).

If you bypassed Framework Manager, you'll need to create a new model for this. Create the model and import your cube. Next, create a query with two calculated items. First one call "Key" with a static 1. The second item is "Day of Week" with the expression: _day_of_week (current_date,7)

Create a parameter map currentDayOfWeek based on that query, with Key as the key and Day of Week as the value. Publish overwriting the existing cube package. Don't worry, this shouldn't break anything as the cube structure remains the same.

Now you have a way of getting the day of week in a dimensional query:
#$currentDayOfWeek{1}#

You can then wrap the expression returning the current week with the lag function to get the correct week:

lag(
item(filter([Cube].[Dimension].[Hierarchy Year].[FiscalWeekDate],[Cube].[Dimension].[Hierarchy Year].[FiscalWeekDate].[Current Week Flag]=1) ,0)
, if( #$currentDayOfWeek{1}# <7) then (1) else (0)
)

So if the currentDayOfWeek returns 7, it will be lag(item(...),0), returning the current week, otherwise it will return lag(item(...),1), returning the previous member.