Using AGGR in a chart

[#1]

Hi,

In order to calculate a Revenue in a chart, i thought i need to perform 2 steps:

=Sum(aggr(Sum($(Rev)),Campaign_ID))

Let say the $(Rev) is a variable with an expression that depends on the Campaign ID for its calculation.

So, I used the aggr(Sum($(Rev)),Campaign_ID) to calculate correctly the revenue per Campaign ID,

and then i used the external Sum() so the calculated revenue will be split according to the chart dimension.

But it doesn't work.

For Example, when i use this table this expression will work:

Date	Campaign	Revenue
21/6/12	1	250$
22/6/12	2	100$
23/6/12	3	55$

But when i use only the Date dimension in the chart, it will not work:

Date	Revenue
21/6/12	405$
22/6/12	0$

How can i make the expression works fine regardless of the dimension i choose?

Thanks,

Guy

[christian77]

Hi,

Use,

=Sum(aggr(Sum($(Rev)),Campaign_ID, Date))

or

=Sum(aggr(Sum($(Rev)),Date, Campaign_ID))

I need more info. A campain has... how many dates?

Do different campains have the same date?

luck

[#3]

Hi Guy Davidovich,

Please correct me if i'm wrong.

You are using aggr(sum(expression),diimension) to calculate revenue. & on above that you are using sum on this aggr result.

If you are doing so, may be you are approaching in a wrong way cause you are trying to use arthematic function on a arthematic function. which may not work for ur case. Can you give a try to use total function.

hope this helps & correct me if i'm wrong.

thank you

Meher

[#4]

Hi,

The Problem of using the expression

=Sum(aggr(Sum($(Rev)),Campaign_ID, Date))

is that is limited to Date as a dimension, and i using different dimensions (i let the user choose the dimension he likes, i think it called "flexing dimensions").

So, if the user choose another dimension to be shown in the chart, say Region, it will not work.

Any new suggestion?

Thanks,

Guy

[#5]

Hi Guy,

As it's not limited to dimnsion, If you let user choose a dimension dynamically, It changes your aggregate value

but the dimensionalities which you are using in aggr functions have must contain your chart dimension. you can ask user to select a dimension but the dimensions which you are using in chart have to be there in aggr function & by choosing dimension dynamically it chnages your aggr value.

Hope this helps

thank you

Meher

[christian77]

Hi:

There is a funcktion

GetCurrentField(YourGroup)    ///The QV color-sintax algrithm DOES NOT WORK HERE. Don't worry, it does it.

sum(aggr(Field,<YourGroup>))  ///I don't know if this one works fine

Think that aggregating is not easy. Try to do it with months and weeks. It will split both of them wrongly.

[#7 · Accepted solution]

I managed to do this by using expression similar to this one:

='Sum(Aggr(Sum(PixelSum),$(=Concat(_dimension,',')&',Campaign_ID')))'

The $(=Concat(_dimension,',')&',Campaign_ID' build a string consists of the fields the user chose + Campaign_ID, so the AGGR will take place with consideration of the dimensions of the charts, and still make the calculation for each Campaign.

Thanks A lot for helping ,e get the solution!

Guy