Hi ,
My time dimension looks like this , with 2 levels
2019 - Fiscal year
Jul 2018 - months
Aug 2018
...
May 2019
Jun 2019
There is no quarter level in the dimension and business doesnot want quarter level to show in analysis studio .
but for a report , we have user can select any of the months and it should calculate its quarter .
For example if the user selects nov 2019 , then there will be two calulations
one showing november sales and other showing the sum of Nov2019,Dec2019 and Oct 2019 sales , that quarter .
How do I get the Nov 2019 ,Dec 2019 and Oct 2019 when Nov 2019 is selected.
Is there a way to calculate this without having the quarter level introduced.
Any help is appreciated.
If you can have Business Key for your Month level as a number ( e.g. Jan 2019 => 201901, Nov 2019 => 201911)
then you can come up with an expression to filter required months for a quarter.
What are you using as a Data Source for your reports? (relational package, DMR, cube, etc) ?
I wanted to use something like this to get it ,
if(substring(caption(#prompt('prmMonth','mun','[2019/Jan]')#),6,3)='Jan')
Then
(0)
Else
(0)
I am trying to get the month name that is selected and then accordingly get the quarters by lag or lead dimensional function . on if else
The issue here is when i add a substring on top of the caption it gives me an error , doesnt macro and substring have some issue here.
Thanks,
So I assume you are using a dimensional source like DMR or cube. Right?
Anyway even if you get your month member correctly how are going to figure out how many leading or lagging members to add to your quarter?
Using a numeric key would be so much easier.
I will have an if else with 12 months to pick the correct quarters . Yes adding a business key would be appropriate.
But i would like to know how to get this expression working.
f(substring(caption(#prompt('prmMonth','mun','[2019/Jan]')#),6,3)='Jan')
Then
(0)
Else
(0)
For dimensional stuff use dimensional functions. E.g. filter
I think i cannot use filter to check if a members description has 'Jan' in it . Something with macro and substring .
In macro or regular expression use dimensional functions only instead of mixing. Unfortunately I do not have any dimensional package handy to try
I assume you have a Cube/DMR.
You need to use Dimensional Functions to calculate a Quarter from a Selected Month,
Then you can define Data Items in your query as follows:
// Selected Month is coming from a Prompt ?par_month?
[Selected Month] := [great_outdoors_cube].[Years].[Years].[Month]->?par_month?
// Set of all months for a year of a Selected Month
[Selected Year] := children( ancestor ([Selected Month] , 1) )
// Month Index is an index of the First Month in required Quarter
// We have 12 months in Selected Year
// First calculate a Selected Month Index using count(1) for each Month up to Selected Month
// Then calculate First Month of a Quarter where Selected Month is
[Month Index] := floor(count ( 1 within set periodsToDate ( [great_outdoors_cube].[Years].[Years].[Year], [Selected Month] ) )/4) *3
// Get Quarter Months for a Quarter where Selected Month is
[Quarter Months] := subset ([Selected Year], [Month Index], 3 )
Essentially you need to derive a zero-based first month of quarter index from a month count as follows:
1,2,3 => 0
4,5,6 => 3
7,8,9 => 6
10,11,12 => 9
// Final Result
[Total Quarter Months] := aggregate(currentMeasure within set [Quarter Months])
See attached Report ( C10, PP cube Great Outdoors)
Please let me know if you need more clarification
Hi Andrei ,
Thank you so much for taking your time to give your solution .
The only issue I have is i think floor is not rounding to nearer upper integer . But other than that everything is working perfectly .
I cannot get this answer from even IBM support yet .
This is really going to help me to complete the report and understand the power of dimensional functions..
Have a good christmas and new year .
Thanks,
Hi Andrei,
I am having issue when i select sep 2019
jul 2019
aug 2019
sep 2019
oct 2019
...
jun 2020
now my index is returning 3 which is correct.
but quarter months is
[Quarter Months] := subset ([Selected Year], [Month Index], 3 )
giving result as sep 2019,oct 2019 and nov 2019
whereas the expected result is july 2019,aug 2019,sep 2019..
What is your expression for the [Month Index]?
What ever you gave
[Month Index] := (count ( 1 within set periodsToDate ( [great_outdoors_cube].[Years].[Years].[Year], [Selected Month] ) )/4) *3
I did not use floor as cognos itself rounding to nearest integer .
Well, floor was there for the reason. The idea is to calculate the first month index(zero based) in the required quarter.
At least you could've tried my original expression before jumping the gun
:-)
my bad , i was too hurry ,now i see adding floor gives right values. Let me read about floor .
Hi andrei ,
So there is still issue happening after adding floor .
Issue happens for 3 months
Jan 2020 , Apr 2020 and May 2020
Example Jan 2020 - Count is 7 as jan 2020 is 7th in order .
Now floor (7/4) - floor(1.75) is 1 and then index 1*3 = 3 .
so this will result the quarter months oct 2019 ,nov 2019 and dec 2019 .
My expressions are same as what you have asked to try .
Try:
floor((count([required expression])-1)/3)*3
Anyway you got the idea.
Just make it work for your attributes.
You are the developer, aren't you?
It's not the rocket science. Just simple math
Yes, i can add logic accordingly, just wanted to let you know. I got the idea.Thanks
Actually the floor function should be replaced with ceiling(many years of Cognos work completely brainwashed me :'( ).
So this should finally work:
Ceiling( count([required expression])/3 -1)*3
Essentially you need to derive a zero-based first month of quarter index from a month count as follows:
1,2,3 => 0
4,5,6 => 3
7,8,9 => 6
10,11,12 => 9
Thank you :)