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

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

MK_2022 — Fri, 02 Sep 2022 00:36:55 GMT

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

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

MK_2022 — Fri, 02 Sep 2022 12:38:39 GMT

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

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

tresB — Fri, 09 Sep 2022 07:25:29 GMT

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. 😞

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

Anonymous — Fri, 09 Sep 2022 18:26:07 GMT

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)