Sumif based on aggr

[#1]

Hello,

I need to sum the sales of all the weeks in a given period of time, which have sales above the average sales of the weeks within that period. Part of the sales data is daily and part of it is weekly. For example:

item	day	week	sales
A	1	40	50
A	2	40	40
A		41	70
A		42	95

In this case the sales in week 40 are 90, in week 41 are 70 and in week 42 the sales are 95. The average weekly sales for item A is 85. Therefore, the weeks with sales above the weekly average are weeks 40 and 42. I need an expression that will give me:

item (dimension)	sales (expression)
A	185

I know that I first need to aggr based on the “week” field. My problem is to create a condition on the aggr grouping, which will pick up and sum only the weeks that are above the average.

Thanks!

[#2]

try something like this

If(Sum(Total <Item,Week> Aggr(Sum(Sales),Item,Week))>Avg(Total<Item>  AGGR(sum( Sales),Item,Week)),  sum(Aggr(Sum(Sales),Item,Week)))

[MayilVahanan]

Hi

PFA

[#4]

Hi Devang, unfortunately this doesn't work...

[#5]

Hi Mayil, i need to solve this without modifying the script. i need to do this on the expression level.

[MK_QSL]

pls check attachment...

[#7]

Hi Manish, your solution is very close to what i need. the only problem is that there's more than one value under the "item" dimension, and TOTAL ignores that. for example, if the data is:

item	day	week	sales
A	1	40	50
A	2	40	40
A		41	70
A		42	95
B	3	42	100
B	4	42	100
B		43	250

The result needs to be:

item (dimension)	sales (expression)
A	185
B	250

Thanks!