AGGR problem

[jeevays7]

Hi All,

I have the table like this,

Date	Name	Value
1-Jul-17	A	10
1-Jul-17	B	20
1-Jul-17	C	30
1-Jul-17	D	12
1-Jul-17	E	45
2-Jul-17	B	5
2-Jul-17	D	12
2-Jul-17	E	15
2-Jul-17	F	10

Now i want the result in text box is sum of

(value of today - value of yesterday)/(value of yesterday) for

what are the name available in today.

Result calculation:

when you are select the date 2-Jul-17, the text box should show:  -1.41667

	Name	2-Jul-17	1-Jul-17	=(tdy-yestdy)/yestdy
2-Jul-17	B	5	20	-0.75
2-Jul-17	D	12	12	0
2-Jul-17	E	15	45	-0.66667
2-Jul-17	F	10	0	0
This value comes in text box				-1.41667

Please help.

[jmvilaplanap]

Hi,

Try with this expression:

(sum({<date={$(today())}>}value) - sum({<date={$(today()-1)}>}value))/sum({<date={$(today()-1)}>}value)

[sunny_talwar · Accepted solution]

Try this

=Sum({<Date = {"$(=Date(Max(Date)))", "$(=Date(Max(Date)-1))"}>}Aggr(

(Sum({<Date = {"$(=Date(Max(Date)))"}>} Value)-Sum({<Date = {"$(=Date(Max(Date)-1))"}, Name = {"=Sum({<Date = {""$(=Date(Max(Date)))""}>} Value) > 0"}>} Value))/Sum({<Date = {"$(=Date(Max(Date)-1))"}, Name = {"=Sum({<Date = {""$(=Date(Max(Date)))""}>} Value) > 0"}>} Value)

, Name))

[effinty2112]

Hi Jeevay,

=sum(Aggr((sum(Value)-Sum({$<Date ={'$(=Text(Date(-1+max(Date))))'}>}Value))/Sum({$<Date ={'$(=Text(Date(-1+max(Date))))'}>}Value),Name))

returns

-1.4166666666667

Cheers

Andrew

[jeevays7]

Thanks to all.