Breakdown of Stats by Month (user input date range)

[Jembo]

Hello all.
I need to build a report that allows the user to input a date range and obtain stats for that range.
The bit I am having difficulty with is that the display needs to break down the stats by month (and probably year - although the sponsor has not specified this, as yet), according to the date range input. So the display will read something like;
<Dim1> <Dim2> <Dim3> <Month1> <Month2> <Month3> etc.

I need to use the Actual Values if present, if these values are not present, then I need to use the Estimated Values.

I have created a variable 'Usage per Month' which goes;

=If IsNull(<Actual>) Then RunningCount(<Estimated>;FormatDate(ToDate(Truncate(<EntryDate>, "MM"),"MM"),"MMM YYYY") Else RunningCount(<Actual>;FormatDate(ToDate(Truncate(<EntryDate>,"MM"), "MM"), "MMM YYYY")

but I am getting a syntax error.

Is what I am after possible with BO and if so, which functions should I be using (i.e. where am I going wrong)?

Cheers

Jembo

 

[Jembo]

I think I may have resolved this (but I have no data to check!).
I have created a variable <Entry Date (Month)> which is

=FormatDate(<Entry Date>, "MMM YYYY")

and then another variable <Usage per Month> which is

= If IsNull(<Actual>) Then RunningCount(<Estimated>;<Entry Date (Month)>) Else RunningCount(<Actual>;<Entry Date (Month)>)

I am not getting any syntax errors so do you think this will work?

Jembo

 

[blom0344]

I am at a loss what you want to achieve, but technically the construction seems to work as it should.
The running count however will also increment for null values, so in my report

 = If IsNull(<Actual>) Then RunningCount(<Estimated>;<Entry Date (Month)>) Else RunningCount(<Actual>;<Entry Date (Month)>)

has the same effect as:

= RunningCount(<Actual>;<Entry Date (Month)>)

Ties Blom
Information analyst

 

[Jembo]

What I am doing is...
Dim1, Dim2 and Dim3 are Company, Organisation, Franchise.
A Company can own several Organisations.
An Organsistion can have several Franchises.
So you get a 'fan' structure.

The stat counts (for example data usage) need to be broken down by month, hence the need to be broken down according to the date range input by the user. So if the user inputs a date range from 01/01/2005 to 31/06/2005, then there will be stats data for 6 months in separate (monthly) columns.

I think for now I will eave the variables as they are until I get some data I can play with.

Thanks for the reply.

 

[blom0344]

If you create a new dimension by a variable like :

 FormatDate(date,"MMM YYYY")

Measures will roll up to this new dimension. I am puzzled what the runningcount exercise should accomplish. This is something else than you expect, perhaps?

Ties Blom
Information analyst

 

[Jembo]

I was using the RunningCount to (hopefully) get a count broken down per month. As a non-accomplished BO user I posted the thread in the hope that someone could show me the 'error of my ways'.

There are many ways to skin a rabbit - but which is the best way? That's basically what I am saying. Will what I have done work or is there a different/better solution?

Jembo

 

[blom0344]

Native solution would be to either use a section/break on the new dimension or to use extended syntax.
The runningCount will just work as a sort of index for the table rows, which is nice , but useless in your case.

Look up how a break works, is probably what you want

Ties Blom
Information analyst

 

[Jembo]

No, a break won't work. I need the table in one piece (for exporting to Excel) and the sponsor wants the months within the specified date range to appear as separate columns within the report.

Can you recommend anywhere to find out about extended syntax?

 

[blom0344]

Oh, but why not create a CROSSTAB instead of a table, this will automatically display the data like you want. Put the new dimension in the column of the block structure and you're almost done

Ties Blom
Information analyst