More > JRiver Media Center 27 for Windows
How to modify GroupSummaryQuery(Album,Rating,1) to use only 3,4,5 star tracks
FenceFurniture:
I've been fooling around with this a bit.....a lot....
EDIT: PROBABLY BEST TO SKIP THIS POST, AND GO TO THE NEXT ONE.
The first thing to note is that I have been using a MUSIC Excel sheet to calculate these ratings via a laborious process of manually entering the star ratings and doing a calculation on that. I had forgotten that in my MUSIC Excel sheet I had weighted the ratings, which means 3* is worth 75, 4* is worth 90, and 5* is worth 100. That means that an album with one 5*, two 4*, and four 3* ( and whatever amount of **,*) will get an overall rating of
(100+90+90+75+75+75+75) / 7 = 82.9
NOT (100+80+80+60+60+60+60) / 7 = 71.4
Referring to the attached screenshot, which is for an album that has four 3*, two 4*, one 5* (like the example described above).
This is a Smartlist view that only displays >= 3* tracks.
Ignore the column "Rt".
"Rate.As.#" is a field I have created for each file, and its calculation is
ifelse(isequal([Rating], 3), 75, isequal([Rating], 4), 90, isequal([Rating], 5), 100)
which is to convert the 3,4,5 ratings to 75,90,100.
Then, I have created an expression column My Exp 1, which is:
ifelse(isequal([Rate.As.#], 75), itemcount(/[Album/] /[Rate.As.#/]),
isequal([Rate.As.#], 90), itemcount(/[Album/] /[Rate.As.#/]),
isequal([Rate.As.#], 100), itemcount(/[Album/] /[Rate.As.#/]))
and this counts the number of 75, 90, 100 tracks and display the total of THAT type for the album each time it finds one.
So, in this case, for each 3* track it displays 4, for each 4* it displays 2, and for the 5* it displays 1.
Next, I have created an expression column My Exp 2, which is:
ifelse(isequal([Rate.As.#], 75), math(itemcount(/[Album/] /[Rate.As.#/]) * [Rate.As.#]),
isequal([Rate.As.#], 90), math(itemcount(/[Album/] /[Rate.As.#/]) * [Rate.As.#]),
isequal([Rate.As.#], 100), math(itemcount(/[Album/] /[Rate.As.#/]) * [Rate.As.#]))
and this gives me a total of , 100 (1 x 100) for the 5* tracks,
180 (2 x 90) for the 4* tracks, and
300 (4 x 75) for the 3* tracks.
BUT NOW I"M STUCK! ::) ;D
What I want to do is add the 4+2+1 together to get 7 rated tracks of 3*,4*, or 5* and
add together the 300+180+100 to get 580 for their total rated values,
divide the 580 by 7 to get 82.9
I don't know how I can do those additions (the division is fine once I have the additions done).
FenceFurniture:
The first thing to note is that I have been using a MUSIC Excel sheet to calculate these ratings via a laborious process of manually entering the star ratings and doing a calculation on that. I had forgotten that in my MUSIC Excel sheet I had weighted the ratings, which means 3* is worth 75, 4* is worth 90, and 5* is worth 100. That means that an album with one 5*, two 4*, and four 3* ( and whatever amount of **,*) will get an overall rating of
(100+90+90+75+75+75+75) / 7 = 82.9
NOT (100+80+80+60+60+60+60) / 7 = 71.4
I'm really, really close now....
I have created a field called Rate.As.# with this calculation:
ifelse(isequal([Rating], ? stars), 0, isequal([Rating], 1), 0, isequal([Rating], 2), 0, isequal([Rating], 3), 75, isequal([Rating], 4), 90, isequal([Rating], 5), 100)
which gives 0,0,0,75,90,100 for ? stars,1,2,3,4,5
and I have changed your key Rating:Key to:
[Rate.As.#]:FileKey()
which gives 0:41356 or 75:41356 or 90:41356 or 100:41356
Then, I modified your save(regex to:
save(Regex(fieldquery(Album,[Album],Rating:Key,2,1),/#(\d+):#/,-2),_trkrat)/
if(compare(if([_trkrat],ListMath([_trkrat],3),), >, 0), if([_trkrat],FormatNumber(ListMath([_trkrat],3),1)%,),)
and I'm using _trkrat as the save name just to differentiate.
However, that is still averaging over ALL the tracks, not just the 3*,4*,5* number of tracks. So it gives an accurate answer if there are no <3* tracks.
So the question is, how do I make it look at only 75,90 or 100 ratings? The (\d+) obviously regards 0 as a digit, it seems. Putting ([75]) didn't work either.
Referring to the attached screenshot, The figures for The Soul Cages through to Brand New Day are correct (agree exactly with my weighted Excel calcs), and Nothing Like The Sun is correct (blank) because I haven't rated any tracks.
However, Anthology (at the top) also has no rated tracks but is giving a 21.4% album rating, (pretty perplexing)
and The Dream Of The Blue Turtles, Sacred Love, 57th & 9th are all incorrect because they have at least one 2* track each, and that is driving the rating down.
Other than that....I'm really, really close now.... :)
zybex:
To exclude the zeroes from Regex, you can use /#([1-9]\d+):#/
But I think it will be as slow as the previous solution as it still uses a fieldquery.
One solution is to just have a static field (non calculated) for this AvgAlbumRating, and just re-calculate/update it using an external script once a day, or as often as you'd like. Not sure if MCUtils can be adapted for that.
FenceFurniture:
--- Quote from: zybex on July 30, 2021, 08:26:05 am ---To exclude the zeroes from Regex, you can use /#([1-9]\d+):#/
But I think it will be as slow as the previous solution as it still uses a fieldquery.
One solution is to just have a static field (non calculated) for this AvgAlbumRating, and just re-calculate/update it using an external script once a day, or as often as you'd like. Not sure if MCUtils can be adapted for that.
--- End quote ---
Thanks zybex.
This /#([1-9]\d+):#/ didn't change anything actually - still getting the same results.
Interestingly, while any change was taking 30 seconds and more yesterday, it is much faster today - probably tolerable (nothing different running or not running in the background). I have no idea why.
Your second solution is a bet out of my depth I'm afraid. (it took me about 6 hours to get this far today....)
zybex:
This should work for you:
--- Code: ---save(Regex(fieldquery(Album,[Album],Rating:Key,1,1),/#([1-9]\d*):#/,-2),_trkrat)/
if([_trkrat],FormatNumber(ListMath([_trkrat],3),1)%,)
--- End code ---
Navigation
[0] Message Index
[#] Next page
[*] Previous page
Go to full version