Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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))
The same.. show zero for product groups with no seasonal product subgroups..
OH k, then that is correct isn't it?
because we are checking whether [Seasonal product subgroup]=[Product subgroup] ?
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.
then you are basically multiplying 0 * Target !
add an if()
if ( ourExpression = 0 , 1 , ourexpression) * Target
That was kind of obvious.. Isn`t it? ) It worked, many thanks!