topic Re: sum group by in QlikView

sum group by

Thu, 28 Apr 2016 19:22:51 GMT

Hello,

I have a table

name value day

A 1 fri

B 1 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

Re: sum group by

sunny_talwar — Thu, 28 Apr 2016 19:53:36 GMT

May be this:

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

Re: sum group by

Fri, 29 Apr 2016 07:00:55 GMT

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?

Re: sum group by

Kushal_Chawda — Fri, 29 Apr 2016 07:07:04 GMT

or may be

=sum(total <name> distinct Value)

Re: sum group by

MindaugasBacius — Fri, 29 Apr 2016 07:09:16 GMT

Or you can use Group by statement in the Script:

LOAD name,

day,

sum(value)

INLINE [

name, value, day,

A, 1, fri,

B, 1, fri,

C, 2, fri,

C, 2, fri

]

Group by name, day;

Re: sum group by

sunny_talwar — Fri, 29 Apr 2016 17:58:15 GMT

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?

Re: sum group by

Fri, 06 May 2016 10:26:01 GMT

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.