Sum if with ABOVE function

Thu, 18 Apr 2013 15:08:04 GMT

Hi experts,

I have a tiny issue haunting me!

I have a table:

Date Load (already cumulated indata source) Stock

01/01/2013 10 20

08/01/2013 10 20

15/01/2013 15 20

22/01/2013 15 20

29/01/2013 20 20

I have a chart with expression: Sum(Load) - Above(Sum(Load)) which shows weekly load:

Date Weekly load (already cumulated indata source)

01/01/2013 -

08/01/2013 0

15/01/2013 5

22/01/2013 0

29/01/2013 5

I would like to add an expression in my chart which shows Stock only if weekly load is positive.

I tried stuff like: Sum(If(Load - Above(Load)>0,Stock)) but it won't work. I think the keyword Above does not work in Sum if syntaxes.

I also tried : Sum(If(Column(1)>0,Stock)) which Column(1) is my Weekly Load, but this will not do either.

I tried with Set analysis syntaxes, but it does not work.

Any ideas?

Thanks experts!

Nicolas

Re: Sum if with ABOVE function

Gysbert_Wassenaar — Thu, 18 Apr 2013 17:33:55 GMT

Try using Date and Stock as dimensions and this expression: aggr(sum(Load)-above(sum(Load)),Date)

Fri, 19 Apr 2013 08:32:27 GMT

Great, and what if I want to add more expressions in this chart (say Stock1, Stock2, Stock3..)

Nicolas!

Fri, 19 Apr 2013 13:38:58 GMT

Problem solved! With formula:

Sum(If(Aggr(Load,Load)>0,Stock))