Print

Please login or register.
1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[FenceFurniture]

The function GroupSummaryQuery(Album,Rating,1) works well enough, but I want to restrict it to only look at 3,4,5 star tracks. The reason for this is that 0,1, or 2 star tracks can be excluded from playing (or even deleted from the library).

For example, say an album has three ***** tracks, one **** track and two rubbish * tracks. GroupSummaryQuery(Album,Rating,1) would give a result of 21 stars divided by 6 tracks = 3.5. However, if those * star tracks are ignored the true rating of the good music is 5+5+5+4 / 4 = 4.75 (or 95% which is my preference for expressing it).

Also, I would like it to display nothing (rather than "? stars") if there are no rated tracks in the abum.

Can someone indicate what I need to modify please?

[FenceFurniture]

So using this:
if(compare(Math(GroupSummaryQuery(Album,Rating,1) * 20), >=, 60), Math(GroupSummaryQuery(Album,Rating,1) * 20)%,)

displays it the way I want (like 72% or 86%), but of course does not eliminate the <*** tracks. So the question is: how to ignore <*** tracks?

[zybex]

Tricky one! I managed to do it with FieldQuery() but had to work around some issues and possible bugs in this function.
- FieldQuery() seems to return a list consisting of only unique values, so it you have multiple tracks with same rating, it only returns one of them (ie, on your example above it returns 5;4;1 instead of 5;5;5;4;1;1)
- To work around that issue, I added an aux field to force distinct values by using FileKey(), but then FieldQuery() now returns ratings for ALL fileKeys between the first and last file in the album, even if they don't exist, and even if they belong to a different album :/
- if [Album] is empty, FieldQuery seems to match all files

So, this is a somewhat working solution, but may give wrong values for some albums until FieldQuery is fixed:
1. create a Calculated Field called "Rating:Key" with this expression:

Code: [Select]

replace([Rating],? stars,0):FileKey()
2. this then gives you the rating% of an Album:

Code: [Select]

save(Regex(fieldquery(Album,[Album],Rating:Key,2,1),/#([345]):#/,-2),_stars)/
if([_stars],FormatNumber(Math(ListMath([_stars],3)*20),1)%,[unrated])
The expression gets all 'Rating:Key' values for files sharing the same Album, then uses Regex to get only the ratings>=3 and remove the FileKey part. Then uses ListMath to get the average, and Math/FormatNumber to convert to a percentage.

[FenceFurniture]

Fantastic, thank you zybex!

I took out the [unrated] so it just displays a blank, but how do I just make it 67% without the decimal place (i.e not 66.7%). (I tried something, but....nup!)

[zybex]

Just set the number of decimal places in FormatNumber() to zero:

save(Regex(fieldquery(Album,[Album],Rating:Key,2,1),/#([345]):#/,-2),_stars)/
if([_stars],FormatNumber(Math(ListMath([_stars],3)*20),0)%,[unrated])

[FenceFurniture]

Yes, thank you. I was just researching that as you posted. I initially messed it up by just removing the 1.

[FenceFurniture]

Crikey, that's resource hungry! If I change the ratings over in one view (where I display the tracks) and then go back to the Albums view (where the expression you wrote is displayed), it locks up for about 30 seconds or so. That leads to two questions (I need to ask because some other fooling around with this that I have done has locked the puta up for 10 minutes....)

1. Can the expression
save(Regex(fieldquery(Album,[Album],Rating:Key,2,1),/#([345]):#/,-2),_stars)/
if([_stars],FormatNumber(Math(ListMath([_stars],3)*20),0)%,)

be put into a field (and maybe save resource)

and 2. Would it work if I replaced Rating:Key in that expression with its calculation which is replace([Rating],? stars,0):FileKey()
or might that possibly make it even more resource hungry?

[zybex]

Try it, but I think it wouldn't help. The problem is the FieldQuery() needing to scan your entire collection to find files with same [Album]. It you have a large collection there's really no way to improve it AFAIK :/

[FenceFurniture]

Try it, but I think it wouldn't help. The problem is the FieldQuery() needing to scan your entire collection to find files with same [Album]. It you have a large collection there's really no way to improve it AFAIK :/

There are about 37,000 files in the collection.

[FenceFurniture]

zybex, in that case, I'm wondering if there might be a simpler approach - which may need a few extra fields, but might perhaps be less resource heavy.
Starting with this: is there a simple way to determine how many 3* tracks are on an album? EDIT: SEE MY NEXT POST - I have kinda worked this out.
If there is then I could do the same for 4* and 5*, and then do a Math operation.

Alternatively, is there a way to restrict it to looking at only files within the album's Filename (path) so that it is not scanning the entire collection to look for tracks within the album?

[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!   

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]

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.

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: [Select]

save(Regex(fieldquery(Album,[Album],Rating:Key,1,1),/#([1-9]\d*):#/,-2),_trkrat)/
if([_trkrat],FormatNumber(ListMath([_trkrat],3),1)%,)

[FenceFurniture]

This should work for you:

Code: [Select]

save(Regex(fieldquery(Album,[Album],Rating:Key,1,1),/#([1-9]\d*):#/,-2),_trkrat)/
if([_trkrat],FormatNumber(ListMath([_trkrat],3),1)%,)

Sorry, but no change.

[FenceFurniture]

Unfortunately we are in clashing timezones. I'm in Australia where it's just after midnight, and I really have to go and pump out some zzzzzzzz. 

[lepa]

Did read really only first post so might have misunderstood something but the same could probably be achieved with global variables which are much faster those group things. variables are re-calculated only if you refresh them so that can be troublesome in some scenarios like in playing now view

Something like this for example

Calculation for smartlist

Code: [Select]

[Media Type]=[Audio] [=Save(0,v_Tracks[Album Artist (auto)][Album])1]=1 [=Save(0,v_AlbumRatingSUM[Album Artist (auto)][Album])1]=1 [=If(Compare([Rating],>,2),SaveAdd(v_Tracks[Album Artist (auto)][Album],1)SaveAdd(v_AlbumRatingSUM[Album Artist (auto)][Album],ListItem(0;0;0;75;90;100,[Rating,0])),)1]=1
Album RT% in the view

Code: [Select]

If(Compare(Load(v_AlbumRatingSUM[Album Artist (auto)][Album]),>,0),FormatNumber(Math(Load(v_AlbumRatingSUM[Album Artist (auto)][Album]) / Load(v_Tracks[Album Artist (auto)][Album]) ),2)%,)

[FenceFurniture]

Thank you lepa. That's works as planned (I just had to make some other mods to the smartlist, such as remove album duplicates and some filtering of folders and sorting parameters. Sorry I'm a little late responding, but I've been working on this for the last few hours before I post what I've done.

Now I do know I'm being fussy, but I got there in the end. 

In the Excel sheet I can round down to the nearest 0.5 by using this:
ROUNDDOWN(MyNumber*2,0)/2 and displaying it in a 1 decimal place cell
That essentially doubles the number, rounds it down to zero places, and then halves it to one decimal place, so
81.1 becomes 81.0
81.4 ► 81.0
81.6 ► 81.5
89.9 ► 89.5
90.1 ► 90.0

The reason for this is that in terms of album rating a 90 or more is an ALL TIME GREAT, 85 to 89.9 is EXCELLENT, and a couple of others.
If I just use ROUND then 89.9 becomes 90 which is not really quite accurate.

However, there doesn't seem to be a Rounddown facility for MC, and I read in another thread that this:
FormatNumber(Math(MyNumber-0.5))
works as a rounddown for positive numbers. That makes sense.

So the Excel formula ROUNDDOWN(MyNumber*2,0)/2 displayed in a 1 decimal place cell becomes
Formatnumber(Math(FormatNumber(Math(Math(MyNumber*2)-0.5))/2),1)

where MyNumber is my version of your original expression:
If(Compare(Load(v_AlbumRatingSUM[Album Artist (auto)][Album]),>,0),
FormatNumber(Math(Load(v_AlbumRatingSUM[Album Artist (auto)][Album]) / Load(v_Tracks[Album Artist (auto)][Album]) ),1)
,)

and the full Expression is now a somewhat more cumbersome:
If(Compare(Load(v_AlbumRatingSUM[Album Artist (auto)][Album]),>,0),
FormatNumber(math(FormatNumber(Math(math(FormatNumber(Math(Load(v_AlbumRatingSUM[Album Artist (auto)][Album]) / Load(v_Tracks[Album Artist (auto)][Album]) ),1)*2)-0.5))/2)
,)

The attached screenshot shows two columns: Alb RT (from your calcs) and My Exp (my modified, more cumbersome version). I'll now take out Alb RT and rename My Exp as that.

I'll continue with the next part in a separate post.

[FenceFurniture]

The first screenshot attached ("Capture1") shows what I have been using to copy data from MC into Excel, and I have modified the new smartlist to reflect this (2nd screenshot "Capture".

However, there are two columns that I can't replicate properly. The first is "MB Size" which gives the total size of the album in MB (which might be 239 or 1816, as can be seen in the screenshot). This view is not a Smartlist, but a view that someone helped create a few years ago. There is no Field called MB Size, and I can't see any calculation when I edit the view. Is there another way I can display the whole album size? The third screenshot shows the details of this view

The second column that needs tweaking is Duration, which is also the whole album duration. So this is the same problem as above, but for duration.

IIRC someone helped me create that view a few years ago, perhaps RoderickGI, so I'll have a search to see if I can find anything relevant.

[FenceFurniture]

Yes, it was RoderickGI that helped me with that. MB Size is an Expression column with Math(round([file size,0] / (1024*1024))). This is a view called Album Size that has the little blue icon containing a musical note (quaver)

That doesn't work in this Smartlist because it is working on tracks.

The solution for this was to incorporate the three custom expressions into that View, and then create the Expression column Alb.Rate.


A very big thanks to zybex and lepa for guiding me through this! 

[lepa]

You can also use whatever view you have created and use that created smartlist only as a helper for calculations by adding the playlist into your view in the "Set rules for file display". After that the smartlist is refreshed every time you access the view.