Aggr(expression) gives 0 as a result

[avantime]

Hi, I have a problem with the expression below. I am trying to add a seasonality percentage to some product subgroups (not all, hence some seasonal product subgroups are null).

The expression works OK when the pivot is expanded to product subgroup level, but once I move to product group level, the totals turn to 0.

Aggr(

SUM(DISTINCT IF([Seasonal product subgroup]=[Product subgroup] AND NOT ISNULL([Seasonal product subgroup]),[Seasonality percent]))

,[Product subgroup],Month)

Hope I made myself clear , maybe you have a solution for me.

[vinieme12]

and the Previous one?

sum( TOTAL <[Product Group]>  Aggr(

SUM(DISTINCT IF([Seasonal product subgroup]=[Product subgroup] AND NOT ISNULL([Seasonal product subgroup]),[Seasonality percent]))

,[PRODUCT GROUP],[Product subgroup],Month))

or

sum( TOTAL <[PRODUCT GROUP], [Product subgroup],Month> Aggr(

SUM(DISTINCT IF([Seasonal product subgroup]=[Product subgroup] AND NOT ISNULL([Seasonal product subgroup]),[Seasonality percent]))

,[PRODUCT GROUP],[Product subgroup],Month))

[avantime]

The same.. show zero for product groups with no seasonal product subgroups..

[vinieme12]

OH k, then that is correct isn't it?

because we are checking whether [Seasonal product subgroup]=[Product subgroup] ?

[avantime]

No, it`s not correct, I am multiplying this seasonal percentage with the current sales target, so the sales target becomes 0. It should show 1 if the Product subgroup is not seasonal.

[vinieme12]

then you are basically multiplying    0  * Target !

add an if()

if (  ourExpression = 0 , 1 , ourexpression) * Target

[avantime]

That was kind of obvious.. Isn`t it? ) It worked, many thanks!