Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

sum group by

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

1 Solution

Accepted Solutions
MindaugasBacius
Partner - Specialist III

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;

View solution in original post

6 Replies
sunny_talwar

May be this:

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

Not applicable
Author

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
Partner - Specialist III

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?

Not applicable
Author

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.