The concept of a running-total is relational concept. Since a relational table can be visualized in only two dimensions it is very easy to understand and visualize running and moving aggregates.
A multidimensional data source, on the other hand, is more complex. You might want the running aggregate on one dimension but not another. When attempting to use the running aggregate functions on a cube you cannot guarantee consistent results, drilldowns might behave unexpectedly, and the entire processing time is increased as the resultset must be processed locally.
Fortunately running and moving aggregates can be handled with purely dimensional functions.
Consider the following request. A line graph that shows the running total of sales for the current and previous years. The ordinal axis shows all months of the current year.
To start create a new line graph report. Create a Query Calculation in the Category area with the following expression (replacing with correct values from your own cube):
descendants ([Cube].[Date].[Date].[Year]->:[PC].[@MEMBER].[20100101-20101231],[Cube].[Date].[Date].[Month] )
This will return the Month descendants of 2010 (see a previous post on a better way of finding the current year).
Create a Query Expression in the series with the following expression:
total([Cube].[Measures].[Sales] within set periodsToDate([Cube].[Date].[Date].[Year],currentMember([Cube].[Date].[Date])))
Run the report. If all goes well you should see the line growing as expected.
It is important to understand the mechanism. The expression in the series is processed for each item in the ordinal axis. The currentMember function will take the member that’s being evaluated. So far January 1 the expression would appear as:
total([Cube].[Measures].[Sales] within set periodsToDate([Cube].[Date].[Date].[Year],[Cube].[Date].[Date].[Month]->:[PC].[@MEMBER].[20100101-20100131]))
When it gets to the October category it will appear as:
total([Cube].[Measures].[Sales] within set periodsToDate([Cube].[Date].[Date].[Year],[Cube].[Date].[Date].[Month]->:[PC].[@MEMBER].[20101001-20101031]))
The periodsToDate function will take the member and create a set of members starting with the first member in the specified level to the member being evaluated. October/2010 would return the set: January/2010, February/2010, March/2010, April/2010, May/2010, June/2010, July/2010, August/2010, September/2010, October/2010.
The next demand is to see the running total for the previous year. In a relational database you might do this with an outer join. Here it’s just a logical extension of the same expression.
total([Cube].[Measures].[Actual] within set periodsToDate([Cube].[Date].[Date].[Year],parallelPeriod([Cube].[Date].[Date].[Year],1,currentMember([Cube].[Date].[Date]))))
This is essentially the same expression, with the parallelPeriod function wrapping the currentMember.
The same concept can be used for moving totals. Instead of the periodsToDate function, you would use lastPeriods().
total([Cube].[Measures].[Sales] within set lastPeriods (5, currentMember([Cube].[Date].[Date])))
This will always create a total for a set of 5 members ending with the member being evaluated.
Here’s a small challenge. Knowing this, how do you do a running-total in reverse?
Hi Paul
thanks for the clear and precise explanations.
My challenge is to calculate a month on month variance using an OLAP source (Powerplay cube).
I have a crosstab with columns = months, rows are 3 measures (this year actual, this year budget, last year actual)
I’d like to add a 4th measure that shows (for all the months YTD) the change in ‘Actuals’ since the previous month (ie 20% growth in August 2010 (change between July and August) , 5% growth in Sep 2010, 5% decline in October 2010) etc.
July August Sep Oct
Actual 100 120 126 119.7
Month Chg % 20 5 -5 <<<< this is the line I need to add
Budget 15 16 17 18
I'm a bit new to MDX – been dabbling with tuples, parallel periods, prevMember etc – none giving me what I need)
(I have some flexibility on the cube design if necessary – but am dealing with very large data volumes)
Would really appreciate any advice.
thx, Gill
Hi Gill,
What you’re describing is a moving-total (well, a moving-change I guess). This is actually a lot easier to accomplish than the running-total I described.
You’ll need to use a calculation that will find the 1 – (Measure / Previous Month Measure). In order to find the previous month measure you’ll need to do a tuple on the Measure and the prevMember() of the month in the column. The magic function you’re looking for is currentMember().
currentMember will return the member of the specified hierarchy that is over the current intersection.
1 – ([Measure] / tuple(currentMember([Cube].[Dim].[Time]), [Measure])) should be what you’re looking for.
Good luck,
-Paul
HI Paul – Great explanation.
I am able to do running count using your suggestion. I was wondering if there is any way to implement running count on a dimension where the level has been suppressed in the cube.
Dimension
Hierarchy
All members
Member1
Member1.1 *
Member1.2
Member2
Member2.1
Member2.1.1 *
Member2.1.2
Member2.2
Member3 *
Say I selected Member 1.1, Member2.1.1, Member2.2, Member3 in the prompt page.
I would like to do a running count on the member. Is this achievable ?
Member1.1 – 1
Member2.1.1 – 2
Member3 – 3
That’s an issue I’ve revisited several times, but I can’t think of any way to get that to work. To the best of my knowledge there is no MDX function which will return the index of a specific member in a set (the reverse of item()). The method I posted about relies on the contiguous members in a single level.
You might be able to cheat with some clever use of report functions. For example you can use the rowNumber() to mimic the running-count, and you’ll be able to use report expressions for whatever calculations you need: rowNumber() / [Query1].[Measure]. Not the best work-around, but the only one I can think of.
Hi Paul – Thanks for the prompt reply !
I am in the midst of playing with a combination of level number and last period to generate a unique id and rank the unique id to give me the count. Painfully long calculations and was hoping your suggestion on Row Number can do the trick. Though I can insert a Row Number (via the Insertable Objects) into a list without a problem however I can’t reference it in a data item as it not a query item. Can you eleborate on the row number ?
Many thanks again for your suggestion!
I’ve been also playing with the idea of rank. According to help the type of ranking (Olympic, dense, etc.) is determined by the type of cube used. rank(1 within set [Test]) was always returning 1, so it looks like powercube rank is probably using Dense ranking. It may be possible that other cubes use different types of ranking, but I only have access to powercubes at the moment.
When you insert the row number into the list you’re using a Report Expression. Since the list is associated with a specific query you can reference data items from that query inside the report expression. For example let’s say your list has Year, Product Name, Sales Total from Query1. You could put a Layout Calculation on the far left column with the following expression: rowNumber() * [Query1].[Sales Total]. That will multiply the Sales Total by the row number. If you’re trying to reference Sales Quantity, which doesn’t appear in the list, you need to include it in the properties of the list (under the properties there is a row called properties, this forces the data item to be included in the SQL, even if it’s not one of the list columns).
Hi Paul…I am waiting for your help for my previous query and could you also help me in finding rank when the column is not in crosstab. I have prompts on the report page, When I am not selecting any prompts the rank is working properly but when I select any prompt rank is not working..the select prompt is not present as row or column in cross tab. It looks like it is calculating rank first and filtering the values based on prompt values.
Appreciate your help.
Thanks.
Hi Paul,
I am working on a simple crosstab report using relational data. I am able to calculate the moving-total in ascending order, is there a way to calculate the moving-total in descending order?
Can you please give me any suggestions?
Thanks,
Aman.
You’re probably doing something like running-total([Measure] for [Attribute]). To reverse it you just need to find the total and subtract the running:
total([Measure] for [Attribute]) – running-total([Measure] for [Attribute])
Thanks for the reply Paul!
In the following examples, moving_total ( [measure], 2 for [attribute])
Example 1 values are correct and if I want to get the same results even if I sort the dates column in desc order what should I do? (moving_total function is simply calculating measure + previous measure value irrespective of the order)
Example 1: dates are in ascending order
Jan 1st moving_total Jan 2nd moving_total Jan 3rd moving_total
A 10 10+0 = 10 20 20+10 = 30 30 30+20 = 50
B 50 50+0= 50 70 70+50 = 120 80 80+70 = 150
C 100 100+0 =100 150 150+100 = 250 300 300+150 = 450
Example 2: dates are in desc order
Jan 3rd moving_total Jan 2nd moving_total Jan 1st moving_total
A 30 30+0 = 30 20 20+30 = 50 10 10+20 = 30
B 80 80+0= 80 70 70+80 = 150 50 50+70 = 120
C 300 300+0 =300 150 150+300 = 450 100 100+150 = 250
Hope my question is clear now. Any suggestions would be appreciated 🙂
It’s clear but very difficult. Since moving totals are applied based on the sorting, you need to first run that calculation, then sort afterwards.
Create the query, A, that has the moving total, then create another query, B, based on A. Since the aggregations are done in A any sorting done in B will not effect the data.
Hi Paul,
I’m trying to calculate running-difference for all the months of a year. Data source is cube and report type is crosstab. My measure is Empcount. I want to get something like this
Jan-11 Feb-11 … Dec-11
A1 10 20 5
A2 8 13 20
and so on.
My problem arises from the fact I get A1, A2 etc. from a dimension, which are children of a category ‘A’. I get A1, A2 by a calculation expression (say DataItemA) given by descendants(filter([cube].[DimA].[DimA] ,caption([cube].[DimA].[DimA])=?p_A? ),1). Till this point I get correct result.
Now when I try to use an expression running-difference([empcount] for DataItemA), it gives me an error saying running difference cannot be used in the context in which it is used.
Any help you can provide will be a great help.
Hi Amit! Welcome to the wonderful world of dimensional reporting.
The running functions are not supported at all in dimensional queries. Which is a good thing, since the running functions are very limiting. Instead you’ll have to use the dimensional functions to get what you need.
If I understand correctly your crosstab shows the EmpCount for each A for each month. You have a peer underneath the A node which shows the total for A for each month. Underneath the total you want to see the running difference of each month.
The A and Total node you already have. The moving difference can be calculated by finding the prevMember() of the currentMember() of the Date Hierarchy.
Try doing this:
Create a new data item called SelectedA: member(aggregate(currentMeasure within set filter([cube].[DimA].[DimA] ,caption([cube].[DimA].[DimA])=?p_A? )),’SelectedA’,’SelectedA’)
This will create a calculated member that you can use in tuples.
Next create the data item you will add to the crosstab:
[SelectedA] – tuple([SelectedA],prevMember(currentMember([Cube].[Time].[Time])))
Hey Paul,
You’re a dimensional reporting genius. I could not understand a 100% of what you had suggested me to do especially in the SelectedA expression:
member(aggregate(currentMeasure within set filter([cube].[DimA].[DimA] ,caption([cube].[DimA].[DimA])=?p_A? )),’SelectedA’,’SelectedA’)
as I thought it would be kind of circular reference but realised the ’SelectedA’,’SelectedA’ part is just string.
But it worked.
There is one thing though that did not happen. I need the running difference or the difference data item to be blank under Jan-11. Presently it shows a value and it is working as per definition but I am filtering the query for 2011 and it shows me the difference for Dec-2010 and Jan-2011, which I’m trying to avoid.
Is it possible to blank it out under Jan-11 even with the complex defintion?
Well, the query is working as expected – it’s showing you the correct difference for Dec to Jan. Hiding the value through a trick in the query is possible, but I suspect it would also decrease the queries performance.
This is a crosstab, yes? What you could do is conditionally hide the value for the first column. Create a Boolean variable, call it “notFirstColumn”. Use the expression “ColumnNumber()1”. Unlock the report and use that variable as the render variable for the value in the intersections.
On other thing I forgot to mention is that I have a total column Total Employees and I need the running difference on the total of A1, A2 etc. something like this.
Jan-11 Feb-11 …Nov-11 Dec-11
A1 10 20 6 5
A2 8 12 18 20
…
An 1 3 3 2
Total Emp 46 50 48 45
Running Diff 4 -3
paul you legend – thanks for this
i was using total([MyMeasure] within set periodsToDate…
but found i needed to explicitly use total([Cube].[Measures].[Sales] within set periodsToDate… for it to work correctly. thank you once again
Hi Paul,
I have to calculate running count on a cube based on the year parameter. also I need to show the prior year running count on a line chart. Please suggest me how do I do it with parameter?
Thanks!
The expression works in the context of a set on an edge. For example, a set of months on the crosstab rows. In what way is the running count supposed to be shown on the year?
If you need to show the months of the year you can do something like descendants(#prompt(‘Year’,’mun’)#,[MonthLevel]). Place that on the rows, and use the total(or count or average)(measure within set periodstodate(etc)). For showing the previous year, simply use the parallel period function as shown above (copied here).
total([Cube].[Measures].[Actual] within set periodsToDate([Cube].[Date].[Date].[Year],parallelPeriod([Cube].[Date].[Date].[Year],1,currentMember([Cube].[Date].[Date]))))
Paul,
I love your blog and have learned a great deal from it. I am trying to do a cumulative line chart showing two years compared to each other. One line shows the current year (i.e. 2011) and the other the prior year (i.e. 2010). I want the line for the current year to stop after the current month has been reached. For example, if it is June 2011. The line for CY2011 would stop at June and not show Jul 2011, Aug 2011, Sep 2011, etc., but I want the line for 2011 to show data for these months. I have tried to use different functions such as filter, exception, etc. but have not had any success. Any help would be much appreciated.
Hi RLM,
I’m glad to hear the blog is helping you. What you’re looking to do is certainly possible. The trick is to use the if() function.
I’ll make the assumption that the categories is coming from the descendants of the current year. So you should have two series in your graph, total(actual within set periodstodate(..)) and total(measure within set periodstodate(parallelperiod())). Rewrite the expressions so they looks like:
if(measure = 0) then (null) else (total(measure within set periodstodate(..)))
and if(measure = 0) then (total(measure within set periodstodate(parallelperiod()))) else (null)
Those expressions would only work if the measure is 0 for future months. If there is data, then another step will be needed.
Normally I try to avoid if statements, but they work well with measures. Don’t try converting it to a case statement, as that would force Cognos to run the query locally.
Hi Paul,
I have the same issue, I am taking categories from previous year as current year all categories are not added in the cube. I wan to show the cumilative chart for all weeks in the previous year. In the current year I want the line to stop at previous week.
My expressions are
if([Cube].[Measures].[Units] = 0)
then
null
else
(
total([Cube].[Measures].[Units] within set periodsToDate([Cube].[TimePeriod].[TimePeriod].[Year],parallelPeriod([Cube].[TimePeriod].[TimePeriod].[Year],-1,currentMember([Cube].[TimePeriod].[TimePeriod]))))
)
for previous year,
total([Cube].[Measures].[Units] within set periodsToDate([Cube].[TimePeriod].[TimePeriod].[Year],currentMember([Cube].[TimePeriod].[TimePeriod])))
But I am getting blanc on the report.
Thanks in advance!
Hi Priya
I don’t see any reason why you should be getting blank on the report. The expressions look fine. Are both series returning blank or only one?
Also, if there’s data for the current week then the Current Year line would continue to this week. The way around that is to create a new data item, call it Current Week:
parallelPeriod([Cube].[TimePeriod].[TimePeriod].[Year],-1,
item(
tail(
filter(
[Cube].[TimePeriod].[TimePeriod].[Week]
, [Cube].[Measures].[Unit]>0
)
)
,0) )
and change the current year series to something like:
if(
count(1 within set periodsToDate(
[Cube].[TimePeriod].[TimePeriod].[Year]
, currentMember([Cube].[TimePeriod].[TimePeriod])))
>= count(1 within set periodsToDate(
[Cube].[TimePeriod].[TimePeriod].[Year]
, [Current Week]))
)
then null
else
(
total([Cube].[Measures].[Units] within set periodsToDate([Cube].[TimePeriod].[TimePeriod].[Year],parallelPeriod([Cube].[TimePeriod].[TimePeriod].[Year],-1,currentMember([Cube].[TimePeriod].[TimePeriod]))))
)
It doesn’t look like there’s a way to post images in the comments section here. If this doesn’t fix it, post a note on the Cognoise forum with screenshots and I’ll help you there.
Thanks for the reply Paul!
I’ve done as you mentioned above I am not getting the line for CY.
In my current year line, I need to show the line until previous week. if i have categories in my current year till 4th week. I need to show the cumilative line till week3. As 4th week has 0 value for the measure, 3rd 4th week cumilative value is same and I am seeing parllel(stright) line from 3rd week to 4th week. I am trying to make 4th week 0 value to null, but still I see till 4th week.
Also, My week value is coming from prompt(week3). but I have catogories till week4 in CY.
I am not sure where(which email thread) to attach screenshot in cognoise.
paul,
I also triedthe below expression for CY, I didn’t see the line for CY.
if(total([Cube].[Measures].[Units] within set tail([Cube].[TimePeriod].[TimePeriod].[Week],1)) = 0)
then null
else
(
total([Cube].[Measures].[Units] within set periodsToDate([Cube].[TimePeriod].[TimePeriod].[Year],parallelPeriod([Cube].[TimePeriod].[TimePeriod].[Year],-1,currentMember([Cube].[TimePeriod].[TimePeriod]))))
)
Hi Priya,
What happens if you remove the check for 0? I can’t see any reason why it wouldn’t work.
Register with the Cognoise forum here:
http://www.cognoise.com/community/index.php?action=register
Create a new topic and post a copy of the report xml. It’ll be easier for me to troubleshoot the problem there.
Hi Paul, I have a query returning me records with a date (also month and year) and like to use running-counts to count the occurence of last 12 month in each of the records
I am using Cognos 8 & Reports
thanks for you support
Andy (guest)
Hi Andy,
Is this a relational data source? You might be able to do something like:
running-count(
case when [Date] >= _add_months (current_date,-12) then [Date] else null end for
[Record])
Dimensional is a little more difficult for this
I have a cube based repport that currently working on a prompt where user selects timeframe.
In the report studio my filter is:
[Standard Data Cube].[Time].[Time].[Month] in (?TimePrompt?)
User now wants to schedule the report where timeframe is YTD.
I tried to replace ?TimePrompt? with [YTD], but it does not work.
[Standard Data Cube].[Time].[Time].[Month] in [YTD])
Any ideas?
Thanks.
When working with dimensional data you need to remember to forget everything you know about SQL. Yes, Cognos will let you make a filter like “[Standard Data Cube].[Time].[Time].[Month] in (?TimePrompt?)” but it’s a very poor way of doing things. First, if you’re referencing the month level in the report, then create a query calculation with “#promptmany(‘TimePrompt’,’mun’,”,’set(‘,'[Standard Data Cube].[Time].[Time].[Month]’,’)’)#”. Use that instead of where you’re referencing the month level. If you’re not referencing the months directly in the report, then stick that in as a slicer.
Now I’m assuming that YTD is a different hierarchy. Even with the silliness of using a filter to limit the data returned by the query, you’ll never be able to filter one hierarchy by members in another. You could try to visualize it by trying to limit the z-axis by (x1, x2, x3). What you can do instead is reference the YTD as the default value in the prompt macro:
#promptmany(‘TimePrompt’,’mun’,'[Standard Data Cube].[Time].[YTD].[Month]’,’set(‘,'[Standard Data Cube].[Time].[Time].[Month]’,’)’)#
If user selects any months, then the data item is set([Month1],[Month2],[Monthn]). If the user doesn’t select any months then it’s [Standard Data Cube].[Time].[YTD].[Month]. No comparing different hierarchies.
Hi Paul,
I am using running count in my query to calculate the number of occuerence of the the data item.Mine is a nested crosstab report.I am using expression as
running-count([Sub Segment] for[Product Segment],[Product Line],[Product Family],[Customer Abbr],[WK Date],[WK Date Month],[Fiscal Year],[Data Source],[Data Type]
))
this expression is giving correct result.But my problem is with with the execution time it is taking to run the report.
Can u please suggest me some better idea to accomplish this.I tried using count,count distinct but none of them is giving me the correct result.
It makes sense that it’s taking a long time. That’s a very large number of fields you’re using to slice the running-count. If your database is Oracle you might be able to get away with using windowing functions, but those are difficult to work with in Cognos.
Does the running count need to restart for each combination of those 9 fields?
Hi Paul,
I am not a Cognos developer (Microsoft BI instead), but I currently work with a team of Cognos guys, who build reports on top of SSAS cubes. Because of their inability to use the “aggregate” function over empty cells in the SSAS cubes they asked me to populate all empty space in the cube with 0s. Of course this is a ludicrous idea – after all cubes are like 90% empty space (typically). Unfortunately, they wouldn’t budge and try to use stuff like total or case statements to convert empty to NULL.
Could you please let me know if when working with empty cells you’d use aggregate and what would be the best practices in this area of Cognos development. As far as I am aware they are using Cognos 10.1.1 if this matters…
Thanks!
I agree that it’s unreasonable to ask to populate the empty cells with 0s. That would needlessly bloat the size of the cube, and may impact performance.
While it is true that null + 1 = null, the aggregate summary function should not behave like that. In Cognos you could write something like: aggregate([Measure] within set set(Member1, Member 2, Member 3)). When converting to MDX it would look something like (pseudocode and simplified from what Cognos would actually generate)
I recommend doing a few tests, ensure that handwritten MDX returns the expected data, then go back to the Cognos team and find out what’s going on.
It may be possible that they’re using the same methodology to work with the cube as they would with a relational database. Using filters and relational functions instead of working only with the olap functions. In such cases Cognos will export the data needed by the query to a local dataset, and run an SQL statement on that. In that case, null + 1 would indeed = null.
“It may be possible that they’re using the same methodology to work with the cube as they would with a relational database. ” -> this is exactly what they are doing. Nobody has any experience with multidmensional reporting and they are all used to working with framework manager (pls excuse my possibly wrong terminology here).
I wish I worked with capable Cogos guys, knowing what they are doing, so that I don’t have to read though Cognos blogs, read through IBM documentation, etc, but what can one do…
I will get back to you with more info on how they go with this little challenge. However, it may take a couple of weeks since we are entering the holiday season.
Thanks heaps for the response and have a merry xmas and a happy new year!
Hi Paul,
Could you please help me with the following requirement: I have a measure called Amount and a dimension with values ‘Payments’,’Estimate’ etc..At present I am using only these 2 values in columns and months in rows. I have created a calculation named ‘XYZ’ as Estimate-Payment. Now I want the rolling total of this calculation
Estimate Payment Xyz
Jan-11 10000 5000 5000
Feb-11 7000 6000 6000
Mar-11 5000 4000 7000
Apr-11 8000 6000 9000
Month values in rows are coming from date dimension:
When I am tying to use the expression in your example I am getting an error
QE-DEF-0478 Invalid coercion from ‘level’ to ‘hierarchy’
Sorry if my question seems silly. I am new to reporting on cubes.
Thanks in advance.
Hi Saray,
Make sure you are putting the hierarchy into the currentMember() function. It looks like you might be dragging the level into it.
Thanks for your prompt response Paul..I will try this and seek your help if required..once again thank you so much..
Hi Paul,
I am able to validate my report now after making the required change as per your suggession.Thanks for that.
Now the problem is the calculation is not returning any data. It is not coming in the crosstab. What could be the problem.
once again thanks in advance. Appreciate your help.
I should kick myself for doing that mistake.. It works perfectly..I am happy now.. 🙂
Once again thanks paul for your valuable suggessions..
Cheers
Sorry for posting too many comments Paul..Is it possible to achieve below requirement?
at each and every row of measure XYZ I want to subtract the value of previous month.
For Ex: for the month of Feb-11 the calculation would be 6000(Feb-11)-5000(Jan-11)=1000
For mar-11 7000-600=1000
and so on.
Estimate Payment Xyz abc
Jan-11 10000 5000 5000 5000
Feb-11 7000 6000 6000 1000
Mar-11 5000 4000 7000 1000
Apr-11 8000 6000 9000 2000
Thank you.
looks like I am still in my weekend hangover..
there is something wrong in requirement.Please ignore my above question.
it is simple calculation estimate-payment
I am sorry for that.
I am unable to delete my post.
Hi paul..One more question.. I have prompts on this report which are working fine all the other columns excpet for this rolling total calculated column.
I am having value prompt which has source as months(Reporting Month) from other time dimension. When I am filtering for any month which doesn’t have data for the months showing on the crosstab, I am still getting the values populated for ‘XYZ’ alone
For example if I select reporting month as Apr-11 I will get all the months <=Apr-11(create month) in the crosstab which is working as expected for all the other columns.
But the calculated fileld 'XYZ' is still populating with the rolling total. how to filter this data?
here is how it looks when I select reporting month Apr-11
Estimate Payment Xyz
Jan-11 10000 5000 5000
Feb-11 7000 6000 6000
Mar-11 5000 4000 7000
Apr-11 8000 6000 9000
May-11 9000
Jun-11 9000
Jul-11 9000
Dec-11 9000
Please let me know if any additional information is required.
thanks,…
This is one of the occasions where you can use an if statement. If all the previous months will always have a value you could probably use something like if([Measure] is null) then (null) else (total(currentMeasure within set ..))
Thats great..thanx paul..you are amazing…It worked like magic..Appreciate your quick response…
I am in learning process of reporing on cubes.your suggestions are helping me a lot.
Cheers,
Saray
I have a crostab with relational data in report studio with rows of:
-Financial Year
-Gender
-Age Group
Then several measures in the columns:
-Count Client
-Service aid Amt
-Service Days
I am trying to find the % difference at the level of Age Group within Gender and Financial Year between the previous financial year and the next.
I have got it to work using a running-difference calcualtion and just financial year but when I add in the nested members of Gender and Age Group to the rows, I am not able to get cognos to point to the previous or next financial year’s data.
Any suggestions
Difficult problem, but not insurmountable. Try using the method I posted here.
Paul,
I am using below expression to calculate the 3 day moving-average in my report, I have only Day level in my Time dimension. The expression working perfectly fine when I have data for the last 3 days.
average([Cube].[Measures].[amount] within set lastPeriods (3, currentMember([Cube].[Date].[Date])))
But, If the measure value is missing (i,e. there is no record from the database for any of the date) then it is calculating incorrectly, meaning the 3 day moving-average calculation taking other date into consideration, my requirement is if the data is missing it should still consider as zero (0) amount and calculate the moving-average. In below example there is no amount for 2/17
my requirement is, still it should take amount as 0 for 2/17 and calculate 3 day moving-average as (35+0+25)/3=60/3=20, instead the above expression doing (20+35+25)/3=80/3=26.67, taking 2/15 value as part of 3 day, and this is incorrect.
Please help me how to consider missing values in the expression.
Date Amount
——- ———-
2/13 100
2/14 50
2/15 20
2/16 35
2/18 25
Hi,
Have you tried generating categories always irrespective of the data in fact. i.e in dimension properties try to change the inclusion to always include.
Hope this helps.
PS: I am not an expert and this is just a suggession.
Mrcool,
Thank you!!! I missed to set always for one of the level. Looks like it is working now. Thank you so much for the quick response. I will keep posted if there are any other issues.
You are welcome Mate..
Hi Paul,
I want to present row number in a list report based on cube.
the row which i want to count is of a grouped data item and the numbers are not consecutive.
(it’s counting the actual rows and not the rows which displayed of the grouped the data item)
because it’s based on cube i can’t user running-total function.
Do you have any idea?
Anyone has an example of periodstodate function created in DMR Framework based on any IBM
Sample like GOSALESDW?
Regards,
PP
Is there any way to do a running-total using a year dimension with a seperate month dimension?
That is the year dimension contains 2012, 2013 etc and the month dimension contains Jan – Dec.
Hi Pete,
It is possible, but it’s nowhere as easy or elegant. At the most basic level you need to run the periodsToDate function twice, once to sum all of the years prior to the year in each row, and again for the months.
For this example I’m assuming that months are nested inside years.
total(
currentMeasure
within set
periodsToDate(
[Cube].[Date Dim].[Month Hier].[All Level]
, currentMember([Cube].[Date Dim].[Month Hier])
)
)
That will the running total for the months, you just need to add all of the total for the previous years:
total(
tuple(
currentMeasure
, [Cube].[Date Dim].[Month Hier].[All Level]->:[YK].[Date Dim].[Month].[All Member]
, prevMember(currentMember([Cube].[Date Dim].[Year]))
)
within set
periodsToDate(
[Cube].[Date Dim].[Year].[All Level]
, currentMember([Cube].[Date Dim].[Year])
)
)
Notice the major difference is the tuple in the beginning. In order to prevent the year being sliced by the month, I’m explicitly tupling on the Month’s all member. In a similar way I’m not interested in slicing by the current year for this, so I’m adding a previousMember on the year.
Hi Paul,
I was following your instruction on how to calculate the trailing totals for the last twelve months but unfortunately the result returned is the “actual” number for the particular month rather than the total of the last 12 months.
I have a cross tab with a prompt to select the calendar month. It looks like:
Location
Current Month Trailing 12 months
Account Level 7 $123 (results returned) $123(results returned)
The cube is like this: -Consolidated Balance
-Fiscal Period
-Fiscal Period
– Members
– All fiscal periods
– Calendar Year
– Calendar Quarter
– Calendar Month
The expression I use for Trailing 12 months calculation is:
total([Consolidated Balance].[Consolidated Balance Measures].[Actuals] within set lastPeriods (12,currentMember([Consolidated Balance].[Fiscal Period].[Fiscal Period])))
I am really new to Cognos report studio and any input is greatly appreciated!!!!
Thanks!
Veirena
Hi Veirena,
Are you showing the months from Fiscal Period inside the rows of your crosstab? The function currentMember() will only work in the context of the month. If it’s not in the context, it will return the defaultMember, which is most likely going to be the All member.