Aggr Function on Higher level

[Fiorrie]

Hi Guys

I am trying to figure out how to aggregate one chart but on the top level its not working unfortunately. My formula is following:

IF(SUM({$<Source={'SO'}>}SO_QUANTITY)>0,
IF(SUM({ $ < bid_status_filter = {'Contracted'} >} qty_contract)<> SUM({$<Source={'SO'}>}SO_QUANTITY),
IF(SUM(AGGR($(vLBP_Recommended_Price),[pn],[bid_request_name],[channel_partner_name])) > SUM(AGGR(SUM(contract_price_usd),[pn],[bid_request_name],[channel_partner_name])),
FABS(SUM(AGGR($(vProfitLost),[pn],[bid_request_name],[channel_partner_name]))*$(vSOQty)),
FABS(SUM(AGGR($(vProfitLost_Contr_Price),[pn],[bid_request_name],[channel_partner_name]))*$(vSOQty))))))

So when I look at the data from pn, bid request name and channel partner name perspective its working nicely. However when the chart goes to fiscal year/quarter or any other view like market/segment, its not working anymore and giving me huge numbers. It is probably missing some AGGR function on top but do not know how to combine.

Any help is more than welcome!

Thanks a lot

Jana

[JordyWegman]

Hi Jana,

What is happening in these variables?

1. $(vProfitLost)
2. $(vProfitLost_Contr_Price)
3. $(vSOQty)

If the variable $(vSOQty) isn't returning the right number (maybe an array), this could be the problem.

Jordy

Climber

[Fiorrie]

Hi Jordy

Those are working correctly on pn, bid & channel name I checked them all, but you are right about not working on higher level e.g. year, market with SO Quantity, that is making the numbers crazy basically which makes sense but I am not sure how to add additional aggr function on top.

Why I separated quantity at the end of vProfitLost_Contr_Price was because when it was together it was not returning correct results.

vProfitLost_Contr_Price

(((Sum(price)

*(SUM(discount_percentage)
- SUM(a)
+ SUM(b)
- SUM(c)
+ SUM(d)
- SUM(e))

*(1-SUM(f))

)))

vSOQty

SUM(AGGR(SUM(SO_QUANTITY),[pn],[bid_request_name],[channel_partner_name]))

[JordyWegman]

Hi Jana,

This due to the dimension Year (or other). Can you also get year into the current aggrs?

Jordy

Climber

[Fiorrie]

Hi Jordy

Yes I tried, it is not working.

[JordyWegman]

Hi Jana,

Can you give maybe two examples (screenshots) where its right and wrong?

Jordy

Climber