sum group by

[#1]

Hello,

I have a table

name     value     day

A               1          fri

A               1          fri

A               1          fri

B               1          fri

C               2          fri

C               2          fri

For day i want to show distinct total value per name

so i tried

=sum(distinct value)

But the problem is, that B is not summarized because we allready have value 1 from A so total will be 3

I tried also with aggr but that is or too slow with distinct or just doesn't work...

Any help? Thanks

[sunny_talwar]

May be this:

Sum(Aggr(Sum(DISTINCT value), name))

[#3]

Hello,

Thank you for your answer. But that i have allready tried and that is not working.

Problem is that i have a graph showing mon, tue, wed, thu, fri, sat, sun as dimension in combination with date island.

So in my case when i do

=sum(aggr(sum(distinct if(dateFromABCtable= DateIsland.Date_NUM, value)), name))

I only get the sum on "mon" dimension whilst others remain empty.

I now am thinking maybe first get distinct from name, and next summarize value or somethng like that?

[Kushal_Chawda]

or may be

=sum(total <name> distinct Value)

[MindaugasBacius · Accepted solution]

Or you can use Group by statement in the Script:

LOAD name,
  day, 
  sum(value) 
INLINE [
name,     value,     day,
A,               1,          fri,
A,               1,          fri,
A,               1,          fri,
B,               1,          fri,
C,               2,          fri,
C,               2,          fri
]
Group by name, day;

[sunny_talwar]

Not sure if this would help, but this Aggr() with NODISTINCT

=sum(aggr(NODISTINCT sum(distinct if(dateFromABCtable= DateIsland.Date_NUM, value)), name))

but this is just a guess, as I am not sure what exactly you are trying to do. Would you be able to provide a sample to look at?

[#7]

Hello,

Finally managed to get some time to work on this.

So in the end what i did was moving the "value" load seperately in the script, created a key to "table" so that user selections were connected througover both tables.

Next i used this statement to calculate:

=sum(if(value_year = Di_Year and value_month = Di_Month, aggr(value, nameKey)))

So here i want the "value" grouped by name where my current graph bar is in it's date range; Summarize these values.

The problem was that my date island is by year/month/day, and my "value" by year/month. My chart is by year/month/day

Very hard to select a good answer because all of them are in a sort of manner correct.