Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for
Did you mean:
Partner - Creator III

AGGR problem

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

1 Solution

Accepted Solutions
MVP

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

4 Replies
Specialist

Hi,

Try with this expression:

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

MVP

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

Master

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

Partner - Creator III
Author

Thanks to all.

Community Browser