Summing a grouped field

1

Hi,

I have this to sum up the value of the field ‘win’ for tiddlers tagged with ‘entry’:

{{{ [tag[entry]get[win]sum[]] }}}

Each tiddler also has a field for the week number.

For each week, there will be several ‘entry’ tiddlers and I’d like to have a list showing their count and the summed ‘win’ field for each week number.

eg.

Week — Entry — Win (total)
42--------- 3--------- 1
41--------- 2 -------- 3
40--------- 4 -------- 6

Hope that makes sense.

I don’t know how difficult this is to do - and thanks in advance
Jon

2
  • It is easy, The hardest part is clearly asking what you want, or what the big picture is.
<$list filter="[[tag[entry]each[week]get[week]sort[]]" variable=week-number>
<!-- for each week number -->

</$list>
  • Perhaps this gets you started?
3

Thanks for the reply.

I already have a table with a line for each week number with the contents of the win field for that line:

Week — Win
42--------- 1
42--------- 2
42--------- 4
41--------- 2
41--------- 1

But what I need to do is to have a total of the win field for the corresponding week:

Week — Win
42--------- 7
41--------- 3

Hope that’s a bit clearer.

Regards
Jon

4

So if I understand correctly, you can have multiple tiddlers with the same week, each with a win field and what you need is to sum the wins, grouping by each week.

How about this:

<ul>
<$list filter="[tag[entry]get[week]unique[]sort[]]" variable="week">
    <$list filter="[tag[entry]field:week<week>get[win]sum[]]" variable="sum"
        <li>''<<week>>:'' <<sum>></li>
    </$list>
</$list>
</ul>

This should first find all the unique weeks and then for each week it will sum that week’s.

5

Hi Maurycy,

Yes, that’s worked!

The formatting is a bit off so I’ll play around with it but the arithmetic is right.

Many thanks
Jon

6

Damn! I forgot something,

there is also a ‘loss’ field for each tiddler which needs to be subtracted from the ‘win’ field.

I’ll try and work it out but it would be a miracle if I come up with it!

7

This should work

<$list filter="[tag[entry]field:week<week>get[win]sum[]]" variable="win">
<$list filter="[tag[entry]field:week<week>get[loss]sum[]]" variable="loss">
<$list filter="[<__win__>subtract[<__loss__>]]" variable="final">
        <li> <<final>> </li>
  </$list>
  </$list>
  </$list>
8

Ah, thanks, but that seems to come up with zero and unfortunately I’m not proficient enough to spot what might be going wrong.

9

Can you please share an example tiddlywiki? You can share the html here or host it on tiddlyhost.

10

Yes I’ll mock something up on Tiddlyhost

11

There you go: https://5rise.tiddlyhost.com/

Thanks
Jon

12

My answer above has some mistakes. I am working on fixing it. Please hang on.

13 
<ul>
<$list filter="[tag[entry]get[week]unique[]sort[]]" variable="week">
    <$list filter="[tag[entry]field:week<week>get[win]sum[]] [tag[entry]field:week<week>get[loss]sum[]negate[]]+[sum[]]" variable="balance">
        <li>''<<week>>:'' <<balance>></li>
    </$list>
</$list>
</ul>

image
image2198×862 84.9 KB

Is this working for you ?

I modified @Maurycy solution using a code given by @Mark_S in this discussion

14 


<$list filter="[tag[entry]get[week]unique[]sort[]]" variable="week">

<$set filter={{{ [tag[entry]field:week<week>get[win]sum[]] }}} name="win" >
<$set filter={{{ [tag[entry]field:week<week>get[loss]sum[]] }}} name="loss" >
<$set filter={{{ [<win>subtract<loss>] }}} name="total" >


* <$text text=<<week>> />
** <$text text=<<win>> />
** <$text text=<<loss>> />
** <$text text=<<total>> />


</$set>
</$set>
</$set>

</$list>

image
image1680×814 41.9 KB

Total win_week.json (611 Bytes)

@Jon have a look. It shows sum and loss and their difference correctly. You will hav to fix the formatting though.

15

That’s works perfectly!

Many thanks
Jon

16

Ah, that’s great - I’ve now got the option of showing each category.

Thanks very much
Jon