Need: Sum column when grouping by field

[blue-mouse]

Hi,

I have some "accounting lists" that include columns with
field1: field - formating: Decimal, Integer-lenght:10, Decimal:2
I use also "split by other field2" - that is currency - GBP, USD, PLN...

I use "sum" for column - "field1" - i need also "sum column when grouping by other field3"
for example by field3: Status = Zrealizowane / Anulowane / (empty)

It doesn't sum correctly when grouping - it shows "sum" for whole column - not for each grouped records separately.

Is possible add "sum when grouping" ?

 

[rob]

Im not sure that we can handle the sum logic for multiple group / split bys.
What I have been able to do is :
If there is no split by selected for the sum options, and the list is grouped then the sum is correct for the current grouped data set.
Heres a screen shot of a test list I made - its grouped on teh category field http://screencast.com/t/34iekdl0G3

 

[blue-mouse]

I hope it is possible to make "sum with split when grouping".

Actually we have "nearly the same" working when we use :
Search/filter = Wyszukaj / Odfiltruj

when we use search /filter by field "Status"
we have OK counting for "Kwota" splited by "Waluta" - when status= Anulowane
we have OK counting for "Kwota" splited by "Waluta" - when status= Zrealizowane

So, grouping is only all values for status (Anulowane, Zrealizowane, (empty)) together in 1 raport (list).

Possibly there is "Only" any bug in "code" that "sum with split when grouping" show total for column - not "search/filter by choosen field -and show all group for every value of choosen field")

The same bug is when we show: 10 records/ page and we have:
first page 1
next page 2
it shows total account for page1 + page 2 together (for all records) on each page 1,2.

Can you analyze it?

 

[rob]

ok i've had a go at getting that to work - the changes are in github - let me know if that works for you.

Cheers
Rob

 

[cheesegrits]

Hmmmm. I'm not entirely sure which is "correct" when showing calculations in paginated lists, or even if there is a "correct" way.

Some folk would think that the calculations should only be for the rows being displayed on the current page, others would say it should be the grand total for all rows currently filtered, regardless of pagination.

I guess the ultimate solution would be to have 'subtotals' (of currently displayed rows) and 'totals' (for all rows in the current filter set) ...

-- hugh

 

[blue-mouse]

Rob:
I have actualized to: github 3.0.6.3-894-g0087ba0

It works for me but i need more pretty:

1. For "Grouping" I need in the same way like for "Sort":
When grouping by field1 (for example: "Status") it should show only for grouped value:
for example: when grouped value of "Status" = Anulowane = anu
should show only: anu&EURO:...
anu&USD:...
anu&GBP:....

scren shot 131b.png

2. I would rather not to use:
anu&EURO:
115.790,00
but: EURO: 115.790,00
(we know that this is "anu" because grouped value of "Status"=Anulowane=anu

scren shot 131b.png

3. I would this to work also for grouped value of "Status"="empty"
and then show values only for "Status"=empty"
screen shot 133b.png

 

[cheesegrits]

I'm going to have to leave this one to Rob. I can see what you are needing, but I'm having problems setting up a test case. I'm just not getting the right split/grouped totals at all.

-- hugh

 

[blue-mouse]

Friendly Bump

Rob, i need more professional look of this, and correct values when groupped field = "empty".

See post #6

For grouping i need exactly the same "look" like for search/filtering

 

[rob]

better look use this css or a variant of:

.fabrikForm dl {
width:200px;
clear:left;
border-bottom: 1px dotted #ddd;
}

.fabrikForm  dt, .fabrikForm dd.splittotal {
clear:left;
width: 100px;
text-align: right;
padding: 5px;
float:left;
}

.fabrikForm dd {
float:left;
 padding: 5px;
}

.fabrikForm  dd.splittotal{
margin-left:25px;
font-weight: bold;
font-style: italic;
}

For grouping i need exactly the same "look" like for search/filtering

not sure i understand?

 

[blue-mouse]

1. For "Grouping" I need in the same way like for "Search/filtering":
When grouping by field1 (for example: "Status") it should show only for grouped value:
for example: when grouped value of "Status" = Anulowane = anu
should show only: anu&EURO:...
anu&USD:...
anu&GBP:....

scren shot 131b.png

2. I would rather not to use:
anu&EURO:
115.790,00
but: EURO: 115.790,00
(we know that this is "anu" because grouped value of "Status"=Anulowane=anu

scren shot 131b.png

3. I would this to work also for grouped value of "Status"="empty"
and then show values only for "Status"=empty"
screen shot 133b.png

see post #6 -screen shots

 

[blue-mouse]

This grouping doesn't show splitted sum for value="empty"



Hi Rob,

The main problem for now, is that this grouping show only "splitted sum" for values of field that are different than "empty".
In real tables i have also value of field = "empty", e.g. when grouping by field="Status" - values can be:
zrea - zrealizowane
anu - anulowane
.... - "empty"

Can you add "empty" as one of possible values for field and can you show "splitted values of Sum" also for "empty"?

See - Post #6, picture 133b.png

 

[blue-mouse]

Friendly bump,

I need small change:
I need for "each grouped value" only its own "splitted suma", not "splitted suma" for whole table...
Just only this what is surrounded with "green line" on my screen shot...

Is possible to make this small change?

 

[cheesegrits]

We're running behind on support at the moment, after the site migration. This is no doubt possible, but it'll have to go in the queue until Rob has time to address it. I've raised a github issue for this:

Tweak to grouped calculations ? Issue #692 ? Fabrik/fabrik ? GitHub

-- hugh

 

[blue-mouse]

Hi Rob, thanks for your post #9 - i used to styling "Show sum when grouping" and this looks better but i have also problem that "Show sum when grouping" is repetable for all grouped values - it show suma each time for all grouped values under "one grouped value".
If this is to much work to make show only sum values for choosen group value - can you modify it to show all splitted by values and splitted by grouping lines only "once for list" - it may be displayed on top or on bottom list or on list-page?

 

[rob]

Can you summairse the settings you are using now? I just tried a very simple test, so a field with sum on (but no split) and a grouped by list and I don't see the same results as you. I think you have "split on" on the element calc as well as grouping, in that case just turn off 'split on'

 

[blue-mouse]

I need on this list amount of money/sum ("Kwota") splitted by currency ("Waluta") - so we use currency: PLN, EURO, GBP and USD.

But try to test more simply issue - without split by currency -- and also are problems:
1. for field "Data" --- field type "Date" ---> not grouping correctly
2. for field "Zamawiaj?cy" --- field type "User" ---> show sum for all grouping person under "single group = 1person" :-(

Added:
Without split by currency - is OK -- for example -- when grouping by
field "Termin dostawy" --- field type "Date" (Zaznaczenie_273.png)

 

[rob]

did you try ensuring no option was selected for "split by" ?

 

[blue-mouse]

Screen shots in post #16 were made when no option was selected for "split by",

 

[rob]

Your site's down for me at the moment, what is the exact url to the list you are adding the calculations to?

 

[blue-mouse]

http://194.181.162.166/cc.joomla/in...resetfilters=0&clearordering=0&clearfilters=0
and
http://194.181.162.166/cc.joomla/in...y=ccjom_cc_zapotrzebowanie___zamawiajacy_auto
and
http://194.181.162.166/cc.joomla/in...group_by=ccjom_cc_zapotrzebowanie___date_time


--- without split by currency

http://194.181.162.166/cc.joomla/in...resetfilters=0&clearordering=0&clearfilters=0
--- with split by currency

this is 1 table but 2 lists