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.

[lironbaram]

hi

have you tried to wrap the function with a sum function

sum(Aggr(

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

,[Product subgroup],Month))

[avantime]

hi, tried it, still gives 0.

[lironbaram]

hi

can you post sample data with the table structure you have

or explain the table structure

[avantime]

Unfortunately I cannot post a sample due to confidentiality issues.

If I expand the table to product subgroup level I get results:

Product Group	Product Sub Group	MONTH	1	2	3	4	5	6	7	8
PG1	PSg1	Target	305460	95738	319622	181777	49724	184927	249616	70971
	PSg2	Target	246183	338147	261436	43794	186648	245677	282944	140115
PG2	PSg3	Target	117936	296263	184027	193911	184876	115335	248524	207129
	PSg4	Target	190405	226588	317346	43199	324043	204601	169732	293143
	PSg5	Target	297457	250278	311145	287273	21407	30657	281116	68074
	PSg6	Target	213314	20337	11136	321465	119045	302248	154511	276011

If I contract the table to Product Group level I get all zeros:

Product group	MONTH	1	2	3	4	5	6	7	8	9
PG1	Target	0	0	0	0	0	0	0	0	0
PG2	Target	0	0	0	0	0	0	0	0	0

[vinieme12]

Try adding [PRODUCT GROUP] TO AGGR() AND TOTAL

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

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

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

[avantime]

Hi,

Did that, not working, I get the same sum for each Month column.

It`s still a progress, not getting zeros anymore

[vinieme12]

Then add month to total

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))

[vinieme12 · Accepted solution]

Or

sum(  Aggr(

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

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

[avantime]

Show zero on Product Groups that have no seasonal Product subgroups...