Cumulative average calculated with two dimensions

[#1]

Hello everybody,

I have a cumulative average based on the 6 previous months of my period. I'm using the following formula :

Round(RangeSum(Above(sum({<PERIOD={*},ID_APPLI-={''}>}APPLI),0,$(=%nbRollingMonths)))/RangeSum(Above(count({<PERIOD={*}>}ID_APPLI),0,$(=%nbRollingMonths))),0.01)

Now, I want to add a second dimension, for instance specific mark given by user. I added the dimension but my graph is not changed...

I tried to use Aggr function but it still not work...

Do you have an idea ?

[swuehl]

What is the order of your two dimensions? Could you upload a small sample file here to the forum (upload available in advanced editor)?

Chart inter record functions like above() will regard the so called column segments, so depending on your order of dimensions and data model, the column segments might interfere with your calculations.

You'll see what I am talking about if you add an expression

=rowno()

The row number numbering will be reset on column segment bounderies.

To overcome the column segment bounderies, you could use the TOTAL qualifier, like

=rowno(TOTAL)

You could also use the TOTAL qualifier with your above() function. To help you more on the syntax, it would be helpful to me if you could post more details about your dimensions / expressions, best with posting a small app.

Regards,

Stefan

[#3]

The order of my dimension are : period, items

Attached, a file explains my problem.

In the 'Average' chart, I got my 'normal' average or an average on 6 or 12 rolling months.

In the 'Average of items' chart, I want to have my 'normal' items average or a 6 or 12 rolling months items average. Here is my problem.

In the 'average (normal data)' chart, I have the 'normal' average.

If I select a specific item and choose '6 rolling month', I have the correct 6 rolling months average in the 'average of items' chart/

If I do not select a specific item and choose '6 rolling month', I don't have the correct 6 rolling months average of my two items.

The only solution I found for the moment is to create 2 expressions in my chart :

Round(RangeSum(Above(sum({<Items={'Item1'},NOTE-={''},PERIODE={*}>}NOTE),0,$(=%nbMoisGlissants)))/RangeSum(Above(count({<Items={'Item1'},NOTE-={''},PERIODE={*}>} NOTE),0,$(=%nbMoisGlissants))),0.01)

and

Round(RangeSum(Above(sum({<Items={'Item2'},NOTE-={''},PERIODE={*}>}NOTE),0,$(=%nbMoisGlissants)))/RangeSum(Above(count({<Items={'Item2'},NOTE-={''},PERIODE={*}>} NOTE),0,$(=%nbMoisGlissants))),0.01)

But it is not a very good solution because I have a lot of items in my application...

Hope it was clear ! 🙂

[swuehl]

Pierre,

that's a bit tricky.

You want to display your line chart with dimension ordered PERIODE, Items (so you get lines per Item, PERIODE as x-axis), but you need to do your

rangesum with dimensions ordered Items, PERIODE (so when using above, look back PERIODE values, per Item). You can do this with an additional advanced aggregation:

Round(

aggr(rangesum(Above( sum({<NOTE-={''},PERIODE={*}>}NOTE),0,$(=%nbMoisGlissants))),Items, PERIODE)

/aggr(rangesum(Above( count({<NOTE-={''},PERIODE={*}>} NOTE),0,$(=%nbMoisGlissants))),Items,PERIODE)

,0.01)

See also attached,

Stefan

[#5]

Thank you Stefan.

But I still have an error. It works fine in this little example but in my application,I got a delta in my results.

In my application (different data than in the example), I got 6.85 average for the Jan11 period in 'normal data' mode. But if I select the 6 rolling month mode, the average for the Jan11 period is 6.66 ! And my rolling period has a lot of bad values.

I think it can be a problem of null value. In my second dimension I got the 'If(Items = 'Item1' Or Items = 'Item2', Items)' expression. I tried to check the 'suppress when value is null' property in the dimension tab and the 'suppress missing' property in the presentation tab, but it does not change anything...

[swuehl · Accepted solution]

I assume we have a different issue here:

We now told QV that we want to aggregate over dimensions in order Items, PERIODE, but to get correct results, PERIODE values must be sorted correctly (chronological). In a chart table object, we can just sort dimension values using sort tab. With advanced aggregation, we miss this option (this a big pitfall in use of aggr() !).

The dimension values to aggr() function are always sorted in load order, I believe.

So if you read in a table with PERIODE values that come in not in chronological order, we get wrong results! I've checked that the load order was chronological with the demo data, but forgot to emphasize this point in my post.

So, please check the load order of your field PERIODE by creating a list box, setting sort option to load order only.

If it doesn't come up in a correct order, we need to handle this. One way is to load PERIODE values first in a correct sorted way, could be just a single field or your master calendar. You then can load your fact table, containing also PERIODE values, but the load order will be determined by the first occurence of each value.

If you were using a separate table to create the correct load order, you can then drop this table at the end of your load if you want.

[#7]

Thank you very much Stefan ! It works fine now !

I forgot to make an order in my SQL query...