Set Analysis with FirstSortedValue

[rcandeo]

In this application I wanna show for each Principal and Subcategory, which is my last Visit and the avg of the values of this visit.

The problem is that my expression is not working properly.

In Box 1: Last Visit is the calculation that is not working.

VISIT calculation is showing correctly, but Last Visit is calculating the average for all Visits, and should calculate only the AVG for the Visit that appears on the table. On this example should be 0.

Last Visit 2 is supppose to be the same calculation with the first $ in the right place, but is doesn´t work showing this null value.

Box 2 is only a simple AVG calculation. I expect to see 3 lines, one for each VISIT (SCREENING, DAY 1/RCP, DAY 85/RCP), but is only showing for SCREENING cause the others are 0. I tried to show all values but shows me a lot of null lines and I don´t know why.

Box 3 is the complete data

Anyone can explain me how to fix this Last Visit Calculation?

Mine is avg(${<VISIT={$(=FirstSortedValue(DISTINCT VISIT,-Date))}>} Value)

[rcandeo]

here is app

[rcandeo]

did not work

[sunny_talwar · Accepted solution]

Try this:

Avg(Aggr(If(FirstSortedValue(TOTAL <Principal, QSSCAT> DISTINCT VISIT, -Date) = VISIT, Avg(Value)), Principal, QSSCAT, VISIT))

[rcandeo]

Thank You so Much. This have the maximum difficult nivel.