Calculation that returns the value according to time period

[lorenmario]

Hello!
I have a report with a gauge chart, need to display this chart my stores and the accumulated value to the selected in a value prompt (the value prompt show the periods, ex.: 2013/01, 2013/02...).

If i selected period, eg 2013/06, the report should display the accumulated value in 2013, up to 06 months.
If i selects for example, 2013/01, the report will only show the value of January 2013.
If i selects for example, 2013/12, the report should show the total value of all the months of 2013, understood?

How do I create this calculation that will return me the revenue value, considering the parameter of time (year) will be spent at the prompt?

It is urgent!
Thank you!

[MFGF]

Assuming it's a relational package, you simply have a filter in your query that retrieves rows from the beginning of 2013 until the date your prompt defines. It's not clear from your question whether the "date" data you have is a date datatype or a string/number (and how it is stored). Because of this we can't give you an example of the filter expression, but maybe it's enough for you to know the approach (above) and figure it out?

Cheers!

MF.

[lorenmario]

MF Hello, thank you for your answer.
Asked the dba about the date / type of the field that feeds the prompt, the field is called Month_ref and is varchar, so I'm using a value prompt.
I know I will not be able to use a function of type Year to extract the value of last year's parameter, because the field is text.

The dba says she can create a new column for me, the type date, al I can create the prompt using this new column.

If the field is of type date, what expression I could use?

Thank you for your help.

[MFGF]

Hi,

If it's varchar then you don't need a new column specifically. Wouldn't you just code your filter as

[Month_ref] >= '2013/01' and [Month_ref] <= ?Mth?

or am I missing something?  Does your data contain other years or just 2013? If so, you could try:

[Month_ref] <= ?Mth? and substring ([Month_ref],1,4) = substring(?Mth?,1,4)

Cheers!

MF.

[lorenmario]

Hi MF!

My database contains many years, so the user can select any year and month as of 2011.

The idea is that when he chooses for example, 2012/08, he sees the average revenue earned in that year (2012) until one month (August).
Forgot to mention that he wants the AVERAGE of revenue, and not the accumulated value of the eight months in this case.

Could understand me?

Tks, tks, tks! :)

[MFGF]

Ok. The aggregation of Revenue is not dependent on the filter for months, though. Have you tried the suggestion I included for your filter? Does it return the correct months? If so, all you need to do is to change the aggregation property of your revenue item to Average.

MF.

[lorenmario]

Mf Hi, good morning!

I'll test here and you will soon return.

Thank you!

[lorenmario]

Hello MF!

the filter worked properly, it's returning all-month period selected in the filter, considering the year, very good!

The only problem is that I can not get the chart showing the average, the pointer always displays the sum of months, but I need to be mean, I've already changed the properties of aggregation, but does not change ...

take a look in the attached report, selected in 2013/06 prompt, the months appear in the list correctly, the value of the count also, I generated an average footer to give the value. In the graph you can see that the COUNT is adding ANNUAL indicator values ​​... but I need to average!

Any suggestions??

Thank you for your help!

[MFGF]

Hi,

Silly question - is the chart based on the same query as the list? If you select your "Default measure" in the chart, is the Aggregate Function property set to Average?

MF.

[lorenmario]

Hello,

Yes, the chart is using the same query from the list.
When I click on the indicator that appears on the chart, the rollup property Agregate appears to Automatic, as you can see in the attached picture.

Tks!
:)

[MFGF]

Hi,

Why haven't you set the Aggregate Function to "Average"? "Automatic" will use the default aggregation behaviour set in the metadata, which I'm willing to bet is sum.

You mentioned that you are looking at revenue values, but your report seems to be using a count of something as the measure? If so, you should probably also define the scope of the count in your expression, too,

eg

count([your item] for [NOME_FANTASIA],[MONTH_REF])

Cheers!

MF.