Difficulty in finding out count using aggr()

[sagarjagga]

Hi All,

I need to count username based on latest rating in selected date range.Below is the sample data.

User Name	Rating	update date
A	1	10/11/2018
b	1	10/15/2018
c	2	11/1/2018
d	0	11/1/2018
A	2	11/5/2018
c	3	11/6/2018

i need to count user name against the latest ratings where update date less than [selected date] . for example if selected will be 11/6/2018 and username will be A then final output will look like.

Rating	count of user name
1	0
2	1
3	0

as for user name A and as of selected date 11/6/2018 we have latest rating 2 (on update date 11/2/2018)

if we select a date as 11/4/2018 the final output will look like

Rating	count of user name
1	2  (as A,B)
2	1 (as c)
3	0

rating 1 has 2 count as both A and B username has latest rating 1 on update date 10/11 and 10/15 respectively as of selected date 11/4/2018. 

Note - selected date field is a field which i am pulling from date table which contains all calendar date. the use of selected date field is just to select a range of update dates upto selected date

for instance - {<[update date]={"=${<=max([selected date])}"}>}

regards

[sunny_talwar]

Try this

Dimension

=Aggr(FirstSortedValue({<[update date] = {"$(='<=' & Max([update date]))"}>}Rating, -[update date]), [User Name])

Expression

=Count(DISTINCT {<[update date] = {"$(='<=' & Max([update date]))"}>} [User Name])

[sagarjagga]

Thank so much sunny. The above solution is working fine if i have distinct update date against usernames but if i have same update date against a particular username then it is giving null rating against same user name. for example if my sample data is like
Thank so much sunny. The above solution is working fine if i have distinct update date against usernames but if i have same update date against a particular username then it is giving null rating against same user name. for example if my sample data is like

User Name	Rating	update date
A	1	10/11/2018
A	3	10/11/2018
b	1	10/15/2018
c	2	11/1/2018
d	0	11/1/2018
A	2	11/5/2018
c	3	11/6/2018

if we select a date as 11/4/2018 the final output will look like

Rating	count of user name
1	1 (B)
2	1 (as c)
3	0
NULL	1(A)

it is counting A username in null as A has same update date in the selected range. is there any way to handle that.

Regards

[sunny_talwar]

What are you hoping to see in this case? Does A get calculated for both 1 and 3? If that is what you need then change your calculated dimension to this

=Aggr(
FirstSortedValue({<[update date] = {"$(='<=' & Max([update date]))"}>}Rating, -[update date])
, [User Name], Rating)

[sagarjagga]

as per my requirement, A should be calculated for highest rating among ratings. for below sample

User Name	Rating	update date
A	1	10/11/2018
A	3	10/11/2018
b	1	10/15/2018
c	2	11/1/2018
d	0	11/1/2018
A	2	11/5/2018
c	3	11/6/2018

output should look like

if we select a date as 11/4/2018 

Rating	count of user name
1	1 (B)
2	1 (as c)
3	1(A)

[sunny_talwar · Accepted solution]

In that case try this

=Aggr(
FirstSortedValue({<[update date] = {"$(='<=' & Max([update date]))"}>}Rating, -([update date] + (Rating/1E10)))
, [User Name])

[sagarjagga]

Thank you so much sunny.