6 Replies Latest reply: Mar 16, 2012 8:05 AM by Pierre Philouze

# Cumulative average calculated with two dimensions

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 ?

• ###### Cumulative average calculated with two dimensions

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

• ###### Re: Cumulative average calculated with two dimensions

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 ! :-)

• ###### Re: Cumulative average calculated with two dimensions

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)

Stefan

• ###### Cumulative average calculated with two dimensions

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...

• ###### Re: Cumulative average calculated with two dimensions

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.