Discussion Board for collaboration related to QlikView App Development.
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
In that case try this
=Aggr( FirstSortedValue({<[update date] = {"$(='<=' & Max([update date]))"}>}Rating, -([update date] + (Rating/1E10))) , [User Name])
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])
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
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)
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) |
In that case try this
=Aggr( FirstSortedValue({<[update date] = {"$(='<=' & Max([update date]))"}>}Rating, -([update date] + (Rating/1E10))) , [User Name])
Thank you so much sunny.