Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a field in my script called 'Name' with following values:
John
Mark
Aaron
Tomas
Benjamin
I cannot change the load order.
I then have a report with a separate section for each name. In my title, I am using the following:
=Name &' - '&FieldIndex('Name', Name)&'/'&FieldValueCount('Name')
The problem is, whilst my report loops through 'Name' alphabetically (which is what I want), the field index is based on sort order, so I get the following titles:
Aaron - 3/5
Benjamin - 5/5
John - 1/5
Mark - 2/5
Tomas - 4/5
What I want is:
Aaron - 1/5
Benjamin - 2/5
John - 3/5
Mark - 4/5
Tomas - 5/5
Any ideas? Needs to be totally dynamic as I will never know what the names are, nor how many different names there will be.
Thanks,
Jess
Try this
=Aggr(Name &' - '&(FieldValueCount('Name') - Rank(Only({<Name>} Name)) + 1)&'/'&FieldValueCount('Name'), Name)
What if you try using Rank(Name)? Does that solve your issue?
Rank seems to be going the opposite
May be this
=Name &' - '&(FieldValueCount('Name') - Rank(Name) + 1)&'/'&FieldValueCount('Name')
Hi Sunny,
Thanks for the suggestion. Unfortunately, it doesn't work outside of the table.
If you see the attached, I've added a text box with your expression. When I select any name in the list box, it always gives me 5/5
Try this
=Aggr(Name &' - '&(FieldValueCount('Name') - Rank(Only({<Name>} Name)) + 1)&'/'&FieldValueCount('Name'), Name)
Perfect! Thank you so much