How to display last month value of any quarter in the report

[inu]

Hi Team,

Requirement :
I have to show principal_outstanding of last month of any querter(FINANCIAL).
Q1- APR, MAY, JUN
Q2- JUL, AUG, SEP
Q3- OCT, NOV, DEC
Q4- JAN, FEB, MAR

Current quarter is 3rd, and we have to display month end data. Hence for the time being data should display for Nov end.
I wrote a logic , which is aggregating all months of selected quarter...logic is

[Fact KPI Summary Monthly].[Time Dimension(Fiscal Calendar)].[Quarter Key]  = cast(
(Case When extract(month, current_date) In (4, 5, 6) Then extract(year, current_date) || '1'
When extract(month, current_date) In (7, 8, 9) Then extract(year, current_date) || '2'
When extract(month, current_date) In (10, 11, 12) Then extract(year, current_date) || '3'
Else
extract(year, current_date) || '4'
End), integer)

what modification i have to do...
Please suggest me...


Regards,
Inam

[venkat01]

i think the result of this is [Quarter Key] = 20163

but is this dataitem or filter in query or what??

and what are the values inside [Quarter Key] dataitem

[New_Guy]

Create a data item that produces member caption using current date and don't forget the 'Q'. And then use the item filter function to filter the member from the hierarchy using the data item you created.
You might have to use item(filter(hierarchy, memberCaption),0) = #sq(data item)#
Let me know if you need the exact syntax or search for item filter.