Filter month

[kgcognos]

In our Time Dimension, all months are showing for 2016. We are using a cube.

I don't want to show the future months in my prompt. How do I filter to only show months <= Current Month?

I get this error when comparing months.
QE-DEF-0480 The operands of '[Month] <= [Sales Cube].[Fiscal Time].[Fiscal Time].[Curr Month]' cannot be converted to consistent dimensional object.

Thank you in advance.

 

[MFGF]

In our Time Dimension, all months are showing for 2016. We are using a cube.

I don't want to show the future months in my prompt. How do I filter to only show months <= Current Month?

I get this error when comparing months.
QE-DEF-0480 The operands of '[Month] <= [Sales Cube].[Fiscal Time].[Fiscal Time].[Curr Month]' cannot be converted to consistent dimensional object.

Thank you in advance.

Hi,

Sounds to me like you are trying to use a detail filter here? That's the wrong approach with members in a cube.

You're going to need to change the item in your prompt query to be a dimensional expression rather than the month level. Typically it will use a periodsToDate() function. Do you just want to see the months for 2016, or do you want to see months from other years too? Assuming the latter, the expression will be similar to:

periodsToDate([your All Time level], [your current month member])

The tricky part here is in identifying which member in the main time hierarchy equates to the current month, which I assume is a relative time member in a different hierarchy? Depending on the type of cube you are using, you may or may not be able to use the linkMember() function to do this

ie

periodsToDate([your All Time level], linkMember([your current relative time month member], [your main time hierarchy]))

Failing that, you would need to construct the MUN of the current month member using a macro. To tell you how to do this we'd need to see what the MUN of a regular month member looks like.

Cheers!

MF.

[Lynn]

Could you use a filter function to omit anything without a particular measure? In theory you'd only have revenue (or whatever) for months that have already happened or begun but not for future months.

This expression is an example.

Code Select Expand


filter (
      members ( [Sales (analysis)].[Time].[Time].[Day] )
      , [Revenue] > 0
)


[kgcognos]

Thank you for the help. Both suggestion works!
We also changed the Inclusion Property of Month in Transformer from Always Include to Include when needed.  This also worked.

[bdbits]

Thank you for the help. Both suggestion works!
We also changed the Inclusion Property of Month in Transformer from Always Include to Include when needed.  This also worked.

The inclusion property is the proper fix (in my opinion). For anyone who does not know, "always include" will show every member of a dimension whereas "include when needed" will only include members associated with a fact.

[kgcognos]

Below lets me display the months for this year. How do i show the current and last year months in the prompt?
periodsToDate([your All Time level], [your current month member])

Thank you in advance.

[MFGF]

Below lets me display the months for this year. How do i show the current and last year months in the prompt?
periodsToDate([your All Time level], [your current month member])

Thank you in advance.

Can you clarify what "current and last year months" means? All months from the current year and the previous year? The current month from the current year and the corresponding month from last year? Something else?

MF.

[kgcognos]

Yes, I would like to show 2016 and 2015 months only. Users can only rerun the report up to last year only.
Thank you