How to change Measure values from YTD to Current Month

[alex_]

Hi,

In crosstab I have YTD and Current Month. I put a filter which returns YTD and measure values [Quantity] relates to these values. Is there a way to show YTD, but [Quantity] correspond to the Current Month.

Thanks.
Alex

[wyconian]

Are you using a cube or going straight to the database?

I had a similar situation the best way I could think of doing this was to restrict the measures in the cube to be YTD, that way you can just use the current year date attributes but the measures will only be added for YTD.

I'm pretty sure someone will come up with a better way of doing that maybe using a tuple including a YTD flag.

If you're not uisng a cube you will probably need some case statements to just give measures for the months you want to see but that is likely to have a big impact on performance.

[alex_]

Thanks wyconian,
I will give some more explanations.

I using cube to data model.

In the report must show data for selected month and YTD.

In the beginning of the report I have prompt page, in witch you have choice to select month you want to see the data. This prompt is tied with the following filter:

[GAC Scorecard (analysis)].[Months].[Months].[Month].[Month Key] = ?pMonth.

As you can see in the expression used "=". Take data only for selected month.
With such filtering, half data reports relating to the selected month is displayed correctly, but others related to YTD - no. /of course :)/

For YTD I'm using the following filter: /the filter is in the same query/   

extract(year, [GAC Scorecard (analysis)].[Months].[Months].[Month].[Month Start Date]) = extract(year, current_date) and [GAC Scorecard (analysis)].[Months].[Months].[Month].[Month Start Date]<=current_date

When changing the expression of the filter to select the month from "=" to "<=", [Quantity] take data for YTD. Which is normal for a greater period is always higher priority. But, provides information that is unnecessary as required for reports. Because [Quantity] needed for the report should relate for the selected month.

I tried to do two queries, the goal was to separate filters, I made Inner Join. So the filter for YTD, not affected by the filter for the selected month, but it returns the following error: ": ,,GEN-ERR-0016 The 'currentMember' function is not supported in the context in which it is used."

Do you have any idea how to separate queries with independent filters.
Or some other approach, whereby [Quantity] refer to the selected month and YTD returns consistent with the data of the selected month.

Thanks.

[pricter]

Its not the best practise to use detail filter we having a cube package.
Create the following query item ([SelMonth])

Code Select Expand

[sales_and_marketing].[Time].[Time].[Month]->?Month?
to get the Selected Month

Another query item to find the YTD Months ([YTD])

Code Select Expand

periodsToDate ([sales_and_marketing].[Time].[Time].[Year],[SelMonth])

Another query item to calculate the YTD Revenue [YTD Revenue]

Code Select Expand

total([Revenue] within set [YTD])

Another query item to calculate the Selected month Revenue

Code Select Expand

tuple([SelMonth],[Revenue])

[alex_]

Thanks Pricter that's good advice.

[pooja]

Hi Pricter,

That is really good solution for Month & YTD. Can you please also advise how to calculate same for QUARTER?


thanks,
P

[MFGF]

Hi Pooja,

Can't you figure this out from the syntax pricter posted? Wouldn't it just be ([sales_and_marketing].[Time].[Time].[Quarter],[SelMonth])?

MF.

[advayaaa]

Hey I am in a similar problem of calculating in Report studio,

Hi I need to create a prompt of date which should have a static value previous month.

I have 3 queryin the report -  Year to Date, Year to date-last year and Financial year - last year.

Can anyone help me in building filter for these 3 query. Any help is appreciated.

I have data in YYYYMM format (preferable) or year and month separately

[Lynn]

This is the same question you asked in another thread:
http://www.cognoise.com/index.php/topic,24095.0.html

The questions I already asked you in that other thread are still applicable.

This thread deals with a dimensional source and you have not yet answered whether yours is dimensional or relational. The answer will be very different depending on which type of source you are using.