QlikView FirstSortedValue not giving latest record with Aggr and Count added in the expression

[MK_2022]

Hi,

IMMEDIATE REQUIREMENT : APPRECIATIVE OF ALL THE HELP..!!!!

I have been struggling to make this expression work on bar chart. Attached is the sample data set and Im trying to get the Riskcategory a Patient is attached to for the latest discharge date(Least GraphRank) within the selected timeframe.

Expression : 

Count(Aggr(FirstSortedValue(total <PatientID> {<PeriodType={"Rolling 12"},AsOfPeriod={"$(vMaxAsOfPeriod)"}>}distinct GraphRank, YTDRollingwithRisk),PatientID)) )

vMaxAsOfPeriod is defined as = max(AsOfPeriod)  

the inside expression of FirstSortedValue(total <PatientID> {<PeriodType={"Rolling 12"},AsOfPeriod={"$(vMaxAsOfPeriod)"}>}distinct GraphRank, YTDRollingwithRisk) is giving the correct value which is the lowest graph rank within selected timeframe for each patient but when I add the Count and Aggr on top of it, gives me the Riskcategory of Oldest GraphRank instead of the latest.

for PeriodType of Rolling 12 and AsofPeriod 202007 the expected result is as below:

Shared Risk : 6, FFS : 3 and Full Risk : 2

Instead Im getting as below which is the category of the oldest GraphRank outside of 202207 and Rolling12.Looks like its not even taking setanalysis into consideration.

Please let me know if Im doing anything wrong within the expression or an alternative way..!!

Thanks

[MK_2022]

tagging @sunny_talwar and @tresesco  for a suggestion.. Thanks

[tresesco]

Hi @MK_2022 ,

Could you please share a qvw file with sample data. Also bit more logical explanation would help. Unfortunately I could not replicate the output with the sample data you shared. 😞 

[Anonymous]

It looks like your expression has one too many closing parenthesis. This is what I get when I apply your expression in a horizontal bar chart, sorted by Numeric Value descending. I did have to select the max AsOfPeriod though. Is that not what you want?

HTH,

JohnInSD

(aka johncaqc)