I have a report of plan numbers and actual numbers in a table. I want to show the inception to date numbers in a new row. To calculate that ITD I have the following variable defined
=RunningSum(<Acwp(Query 4 with PM_Tool)>)/1000
This works until I only want to display the data for the current year on the report, then the calculation only calculates the current year and ignores previous year/month data. Also, the calculation displays the last number total for future months (which have 0 as an actual total).
How can I ‘hide’ the previous years or pull the previous year’s ITD? How can I get the report to display only those totals through the current month end date and not the future months?
I’m really new to BO and have been struggling with this problem for a few days now. Thanks Winnie
I’m not quite sure what you are doing now – but if you are bringing back all of the data, and then applying a filter to only see part of the data, you could use the NOFILTER() function in your Item to Date variable.
You might need to combine that with the use of SUM() … WHERE. You can look here in our Reporter FAQ for how to use this Reporter clause.
I’ve looked at the Where clauses, but since the actuals are numbers and the where clause is for a date I get wrong datatype errors.
If I use the No Filter, then it will display months we don’t want to see?
We are looking at creating a summary table in the universe to hold the summary information. Not sure how that’s going to work either, but it is another option for us.
Did you try the trick the FAQ mentioned – create another “Flag” variable that returns either a 1 or a 0, and test your date in that flag? That is, you want your Flag variable to be 1 when the date is less-than-or-equal to “something”, and 0 otherwise.
Then, use that Flag variable as the test in your WHERE reporter clause.
SUM(NOFILTER WHERE Flag = 1)
=RunningSum((<Acwp(Query 4 with PM_Tool)>)/1000) Where (<Actual Month End Date(Query 4 with PM_Tool)><=)
This is the where clause I’d like to create. However, I keep getting sytax errors on this.
I’m very new to BO – had training and these are the first reports I’m doing. They should really be simple, but are getting more and more complex as users keep changing their requirements and definitions!
= If <= CurrentDate()Then (RunningSum(<Budgeted Cost of Worked Performed $>)+)/1000 Else 0
So now I show 0 in the future columns. This works great on the table. However, on a chart, I have lines that drop to 0 values. Can I somehow hide the line charts or tell the chart to not display a line if the value is 0 or the date is greater than the current date?
(BOB member since 2002-06-17)
(BOB member since 2002-06-18)