max timestamp per user and per day

[#1]

I want to find the rating value at the max date (timestamp) per user and category based on the date that user selects through a calendar object.

vSelectedDate: 17-04-2014

MaxDate : AGGR(max(if(  num(CUST_PRF_CAT_EVENT_TM) < num(timestamp(vSelectedDate + 1)),CUST_PRF_CAT_EVENT_TM)),CUST_USER_ID,PRF_CAT_NAME)

The problems we have are the following

1) The 'CUST_PRF_CAT_EVENT_TM ' date is not correctly filtered by vSelectedDate for all users(i.e. '801CAF65' user).As a result, rating value is not appeared because the matching between two dates is not correct.

2) The MaxDate is not appeared on the correct row data. For example, MaxDate for '239E108A' user and 'Technology' categoty should be appeared in '17/4/2014 21:43' and not in '17/4/2014  18:24' or '17/4/2014  13:16'.

Can you please help me?

[Jason_Michaelides]

Can you provide what your chart should look like if it was working as you desired?

[#3]

Yes, the final chart should look like as being appeared on the following table.

[Jason_Michaelides]

I don't think you need that Aggr() expression.

Drop the MaxDate expression as it duplicates the dimension anyway. As the Rating_Value expression, try:

If(Max(TOTAL <PRF_CAT_NAME,CUST_USER_ID> CUST_PRF_CAT_EVENT_TM) >= vSelectedDate,PRF_CAT_RATING_VALUE)

Hope this helps,

Jason

[#5]

Thank you very much but it is not what i want exactly.

Because, the following expression appears only the rating value for '8E250AD3' user and not for all users.

If(Max(TOTAL <PRF_CAT_NAME,CUST_USER_ID> num(CUST_PRF_CAT_EVENT_TM)) < num(Timestamp( vSelectedDate + 1)),PRF_CAT_RATING_VALUE)

[Jason_Michaelides]

Try:

If(DayStart(Max(TOTAL <PRF_CAT_NAME,CUST_USER_ID> CUST_PRF_CAT_EVENT_TM)) = vSelectedDate,PRF_CAT_RATING_VALUE)

[Jason_Michaelides]

Or:

If(Floor(Max(TOTAL <PRF_CAT_NAME,CUST_USER_ID> CUST_PRF_CAT_EVENT_TM)) = vSelectedDate,PRF_CAT_RATING_VALUE)

[#8 · Accepted solution]

Thank you very much.

I solved my problem using 'total' function in the following expression:

if(CUST_PRF_CAT_EVENT_TM=(max(total<CUST_USER_ID,PRF_CAT_NAME> if(  num(CUST_PRF_CAT_EVENT_TM) < num(timestamp(vSelectedDate + 1)),CUST_PRF_CAT_EVENT_TM)) ), PRF_CAT_RATING_VALUE)

[Jason_Michaelides]

OK cool - glad to be of help. Please mark a correct answer (even if it is your own) to close the thread.