topic Re: AGGR problem in QlikView

AGGR problem

jeevays7 — Thu, 24 Aug 2017 10:35:20 GMT

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.

Re: AGGR problem

jmvilaplanap — Thu, 24 Aug 2017 10:42:00 GMT

Hi,

Try with this expression:

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

Re: AGGR problem

sunny_talwar — Thu, 24 Aug 2017 12:06:15 GMT

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

Re: AGGR problem

effinty2112 — Thu, 24 Aug 2017 16:10:44 GMT

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

Re: AGGR problem

jeevays7 — Fri, 25 Aug 2017 06:02:15 GMT

Thanks to all.