Dimensional Query - Running Total (PeriodsToDate) that stops at current month

[LDJB81]

Hi

I have a chart on a report which displays all months for the current year. On the chart I have a cumulative target measure which my client wants to display all 12 months. The second series is my sales measure which I have coded as follows:

Total([Score_Wholesaler_Sales].[Measures].[Invoice Sales Value]
within set
[Customer],
periodsToDate ([Score_Wholesaler_Sales].[Time].[Months].[Year],currentMember([Score_Wholesaler_Sales].[Time].[Months])))

The problem with this is I cant get the series to stop at the current month because of the currentMember expression. There are only sales up until the current month but the chart has plotted October, November and December (sames values as September) because of the currentMember expression iterates through the 12 months in the categories.

Can anybody help me get the series to stop at the current month?

Thanks
Lloyd

[adik]

Total([Score_Wholesaler_Sales].[Measures].[Invoice Sales Value]
within set
[Customer],
filter(periodsToDate ([Score_Wholesaler_Sales].[Time].[Months].[Year],currentMember([Score_Wholesaler_Sales].[Time].[Months]), tuple(currentMember([Score_Wholesaler_Sales].[Time].[Months], [Score_Wholesaler_Sales].[Measures].[Invoice Sales Value]) > 0))

try this see if it works

[LDJB81]

Thanks for that, the brackets weren't quite correct so I changed it to

Total([Score_Wholesaler_Sales].[Measures].[Invoice Sales Value]
within set
[Customer],
filter(periodsToDate ([Score_Wholesaler_Sales].[Time].[Months].[Year],currentMember([Score_Wholesaler_Sales].[Time].[Months])),tuple(currentMember([Score_Wholesaler_Sales].[Time].[Months]), [Score_Wholesaler_Sales].[Measures].[Invoice Sales Value]) > 0))

It still didnt work though  >:(

[LDJB81]

Just to give a bit more information, the month categories on the chart come from the children of Year value prompt.

descendants ([Score_Wholesaler_Sales].[Time].[Months].[Year]->?pYear?,1)

[adik]

then try first building your months data item by filtering
try something like this:

filter(descendants ([Score_Wholesaler_Sales].[Time].[Months].[Year]->?pYear?,1), tuple([Score_Wholesaler_Sales].[Time].[Months].[Year].[Months], [Score_Wholesaler_Sales].[Measures].[Invoice Sales Value]) > 0))

this should return only the months that have sales

[LDJB81]

The requirement is to display all months as the client wants to the see fixed cumulative target permanently on the chart. This is why its tricky.

[adik]

what data items are you displaying on the rows and columns? is it really required to use the total function?
if you display months and you data is at day level, this will be automatically rolled up based on the default rollup agregate function as defined in framework manager
so you could display the months in the rows for example, and just drag the sales fact to the crosstab measure and it will display values just for the months that have values

[CognosPaul]

I saw this early, but the holidays took 100% of my attention.

There are a few ways of doing this, some more complicated than others. The easiest way I've found is to compare the number of members in the dynamic set (opening period to current member) versus the number of members in the static set (opening period to current month).

In my test case I used the year since it was easiest:

Code Select Expand

if(
  count(1 within set periodsToDate([Cube].[Time Dim].[Time Hier].[(All)],currentMember([Cube].[Time Dim].[Time Hier])))
  >
  count(1 within set periodsToDate([Cube].[Time Dim].[Time Hier].[(All)],[Cube].[Time Dim].[Time Hier].[Year]->:[YK].[[Dim Date]].[YearMainHierarchy]].[Year]].&[2012]]]))
)
then (null)
else (
  total([ספירת תאונות] within set periodsToDate([Cube].[Time Dim].[Time Hier].[(All)],currentMember([Cube].[Time Dim].[Time Hier])))
)

This will work on the month level too. The only hard part will be identifying the current month. Depending on your cube and how it's built, there are a few ways of doing it.

Do you have a current month hierarchy? You could used linkMember. Is the member unique name built like yyyymm? You could use a macro expression to build it:
#'[Cube].[Time Dim].[Time Hier].[Year]->:[YK].[[Dim Date]].[YearMainHierarchy]].[Year]].&['+timestampMask($current_timestamp,'yyyymm')+']]]'#