Aggr function based on an aggregate

[#1]

Dear all,

I am trying to use the aggr-function whereby the values which I want to aggregate are themselves already aggregated. I haven't managed to get it to work, so has anyone here encountered and solved this issue already?

Here is my abstracted formula:

aggr(nodistinct sum( sum(metricA)/sum(metricB) ), categoryA)

Many thanks in advance for your help!

[mike_garcia]

Hi,

Not sure if I fully undertand your question, but probably this is what you are looking for:

Sum(aggr((sum(metricA)/sum(metricB)), categoryA))

If this doesn't solve your problem, please provide more details as to what the end result should look like (maybe an example app).

Mike.

[#3]

Hi Mike,

thanks for your quick reply. Sadly your suggestion didn't do the trick either.

Let me explain the problem in more depth.

I want to aggregate values over calendar weeks and products. That alone seems quite straight forward:

aggr(sum(values), calendar_week, products)

My problem however is that my values aren't simple summable data fields like "cost" or "revenue". Instead these values are already calculated by using some sort of aggregation which is absolute necessary. In my case values that I want to use in the aggr-function are this:

round((sum({$<BE_StatusID = {0,1,2,3}>} BE_Anfrage1) - (count(BE_GclidClean)-count(distinct BE_GclidClean)))/AW_GP)

The formula alone works perfectly. But if I put it into the aggr-function I get nothing but errors. So it must be a limitation of the aggr-function.

I've already tried to substitute the set analysis part with "sum(if(BE_StatusID < 4,BE_Anfrage1,0))" but no luck either. After stripping out piece by piece I eventually ended up only testing something like "count(BE_GclidClean)" as my values within the aggr-function, but again, no results. To me it looks like the aggr-function can't handle any additional aggregation (sum, count, etc) within its expression part.

Do you know a way around this?

Many thanks again.

Denis

[jonathandienst]

Denis

To the best of my knowledge this is not a limitation of the Aggr function. You did not specify how you would like to use the aggregate. To take your code - assuming that you want this as a calc dimension:

=aggr(round((sum({$<BE_StatusID = {0,1,2,3}>} BE_Anfrage1) - (count(BE_GclidClean)-count(distinct BE_GclidClean)))/AW_GP), calendar_week, products)

If you want the sum in an expression:

=sum(aggr(round((sum({$<BE_StatusID = {0,1,2,3}>} BE_Anfrage1) - (count(BE_GclidClean)-count(distinct BE_GclidClean)))/AW_GP), calendar_week, products))

This is syntactically correct and no more complex than many I have used without a problem. If this does not work, then it could be some issue in your data model. If that is the case, I suggest that you post a sample of your qvw file so that someone can look at it in more detail

Hope that helps

Jonathan

[mike_garcia]

I also think it's not a limitation with he Aggr() function. Have you tried to do it step by step? For example, create a Straight table with:

Dimensions:

• calendar_week
• products

Expression:

• round((sum({$<BE_StatusID = {0,1,2,3}>} BE_Anfrage1) - (count(BE_GclidClean)-count(distinct BE_GclidClean)))/AW_GP)

No aggr function involved here, but the Expression column from the above chart is what you'd expect as result when using the agg function:

=Aggr(round((sum({$<BE_StatusID = {0,1,2,3}>} BE_Anfrage1) - (count(BE_GclidClean)-count(distinct BE_GclidClean)))/AW_GP),calendar_week, products)

Also, I echo Jonathan suggestion: maybe a sample QVW posted here might help.

Hope this helps shed some light on the issue.

Mike.