topic Re: Sumif based on aggr in QlikView

Sumif based on aggr

Tue, 05 Nov 2013 20:01:18 GMT

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!

Re: Sumif based on aggr

Tue, 05 Nov 2013 20:08:06 GMT

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

Re: Sumif based on aggr

MayilVahanan — Tue, 05 Nov 2013 20:18:07 GMT

PFA

Re: Sumif based on aggr

Tue, 05 Nov 2013 20:22:12 GMT

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

Re: Sumif based on aggr

Tue, 05 Nov 2013 20:27:20 GMT

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

Re: Sumif based on aggr

MK_QSL — Tue, 05 Nov 2013 20:38:43 GMT

pls check attachment...

Re: Sumif based on aggr

Wed, 06 Nov 2013 17:36:11 GMT

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!