Week Num Tuple

[GeethaKL]

Hi All,
I am working in Cognos 10.2.2 Report studio package developed from Cognos Dynamic Cube.

Its a Crosstab with Status on Rows and Previous Month displayed in weeks and Current Month displayed in weeks on Columns.
and a measure in the Intersection area.

Rows: Status 1, Status 2, Status 3, Status 4
Columns:
Previous Month(date -Weekending Friday1, date-Weekending Friday 2, 3, 4)
Current Month(  date Weekending Friday1, date Weekending Friday 2, 3, 4)
There is no week calculation,. Its actually Months and Dates. Data will get refreshed every Friday.

Measure : Count

Question:
My User asked me if it is week 1 of the month then the calculation should be a tuple and the rest of the weeks it is just count.
The same rule applies for Previous and Current month.

How to achieve
1.Week 1  from the month Member
2. Intersection if it is Week 1 in both Previous Month and Current Month?

Please let me know

Thanking you

Regards
LGK

[GeethaKL]

Hi Team,
IN addition to the above info(May be I am not so clear)

Please let me know the dimensional functions or MDX(Congas Dynamic cube and the database Sql Server 2008) Or
we can insert any SQL to the Query and Join with main query..

I would like to know some guidance here

1. to extract Week number from the Dates(Children of Month)
2. Then the Case expression/syntax  if it is week one . then the tuple calculation else count from the package

Thanking you
Regards
Geetha

[GeethaKL]

Hi Team,

I have tried to do the below logic to get the tuple working. But its static, which we have to change every month by following deployment procedure.
I know if its done in the backend, it will be good performance wise. But the user is in rush and we don't have resources for the back end work.

I would really hope someone to assist here to do dynamically in Report Studio.

My Logic:

1. Day Number:
Expression

total(1 within set
periodsToDate(Calendar Year Month - Level ,currentMember( Calender Date -Hierarchy  )))


2. Static Tuple Condition: (Oct and Nov - First Week Friday of every Month)
    (Oct - 6 First Week Friday, Nov - 03 First Week Friday)

Expression:
Case when [Day Number]
in(6, 3) then([CountTuple])
else([Count])
end


I would like someone's assistance to calculate first week Fridays dynamically in MDX.
(If it is relational Modelling, we could use _day of Week, _add_days and so on..)

Thanks in advance
Regards
GL

[New_Guy]

Hi,
Just a hint, we have to use relational date functions like  _day of Week, _add_days, like you said above to derive the member_Caption of first week's friday and use that to filter out the member using the mdx filter function.
Another option is to have an attribute built on the dates hierarchy something like 'FWF'(First week friday) for the member you want to filter and then use that to filter out the member. I am not sure if it is possible to do this with the cube technology you are using.
Good luck
New guy

[GeethaKL]

Thanks New Guy for responding.
With first option, can you pls let me know how to go about creating member caption of first weeks Friday of every month using Relational date functions.

With your guidance, I would be able to get through it.

Thanks in advance

Regards
GL

[New_Guy]

Hi,
I will write a solution as I don't have time right now. Mean while can you create a data item with the following expression and use it in the tuple condition and this will be taken care of for a year(2018) or more as you require but you have to add a condition with extract year. You have to enter the right date of the first friday for the month to filter out the right date member.

case when extract(month,current_date) =   1      then    item(filter([date].[hierarchy], roleValue ( '_memberCaption' , [date].[hierarchy] ) = '5'),0)
case when extract(month,current_date) =   2      then    item(filter([date].[hierarchy], roleValue ( '_memberCaption' , [date].[hierarchy] ) = '2'),0)
case when extract(month,current_date) =   3      then    item(filter([date].[hierarchy], roleValue ( '_memberCaption' , [date].[hierarchy] ) = '2'),0)
case when extract(month,current_date) =   4      then    item(filter([date].[hierarchy], roleValue ( '_memberCaption' , [date].[hierarchy] ) = '6'),0)
case when extract(month,current_date) =   5      then    item(filter([date].[hierarchy], roleValue ( '_memberCaption' , [date].[hierarchy] ) = '4'),0)
case when extract(month,current_date) =   6      then    item(filter([date].[hierarchy], roleValue ( '_memberCaption' , [date].[hierarchy] ) = ''),0)
case when extract(month,current_date) =   7      then    item(filter([date].[hierarchy], roleValue ( '_memberCaption' , [date].[hierarchy] ) = ''),0)
case when extract(month,current_date) =   8      then    item(filter([date].[hierarchy], roleValue ( '_memberCaption' , [date].[hierarchy] ) = ''),0)
case when extract(month,current_date) =   9      then    item(filter([date].[hierarchy], roleValue ( '_memberCaption' , [date].[hierarchy] ) = ''),0)
case when extract(month,current_date) =   10      then    item(filter([date].[hierarchy], roleValue ( '_memberCaption' , [date].[hierarchy] ) = ''),0)
case when extract(month,current_date) =   11      then    item(filter([date].[hierarchy], roleValue ( '_memberCaption' , [date].[hierarchy] ) = ''),0)
else 'enter the date for december 1st friday' end

#/*
case when extract(month,current_date) =   12      then    item(filter([date].[hierarchy], roleValue ( '_memberCaption' , [date].[hierarchy] ) = ''),0)
*/#

Good luck
New guy