Rank Aggr If condition not displaying any values

[Sai78]

Im trying to display the values in list box based on count of values ...Top 20 or ALL  identifers based on conditions(and  ,or conditions).. can you please help if I'm doing any mistake here ..Thanks in advance

=IF(Att=1,aggr(if(rank(if(
( Status='Missing'and DateType='CaseCreatedDate' )
OR
( [Type] = '$(vCy)' and RelatedEnd >=$(vToday)and RelatedEnd <=$(v30Days))
OR
([Type] = 'PP' and Related2End >=$(vToday)and Related2End <=$(v30Days) )
OR ([approvals] = '0') and [Type] = '$(vCy)' , [Identifier]))<=20,[Identifier]), [Identifier]) 

,IF(Att<>1,IF( if(
( Status='Missing'and DateType='CaseCreatedDate' )
OR
( [Type] = '$(vCy)' and RelatedEnd >=$(vToday)and RelatedEnd <=$(v30Days))
OR
([Type] = 'PP' and Related2End >=$(vToday)and Related2End <=$(v30Days) )
OR ([approvals] = '0') and [Type] = '$(vCy)' , [Patient Identifier]) ))

[sunny_talwar]

Not sure, but could it be related to mis placement of Parenthesis? Not sure what your condition needs are.

[Sai78]

My requirement is to display the Show Top 20  identifier in a list box based on several conditions or Show All

=if(vAtt=1,'Show All','Show Top 20')

expression issue with top 20 section .. could you please help?

I'm attaching the application.

[Kushal_Chawda]

@Sai78 what is the measure to get the top 20?

[Sai78]

@Kushal_Chawda  Thanks!

'count of distinct identifier ' based on 4  below conditions 

( Status='Missing'and DateType='CaseCreatedDate' )
OR ( [Type] = '$(vCy)' and RelatedEnd >=$(vToday)and RelatedEnd <=$(v30Days))
OR
([Type] = 'PP' and Related2End >=$(vToday)and Related2End <=$(v30Days) )
OR ([approvals] = '0') and [Type] = '$(vCy)' 

[Kushal_Chawda]

@Sai78  How would you show Top 20 identifier based on count of identifier? There should be some measure like sum(value) or other. In your attached qvw what is the expected output? Also in your qvw there is not approval field which is there in your condition

[Sai78]

@Kushal_Chawda  sorry for the confusion. please see the attached qlik file and below ..any insight would be greatly appreciated 

my 'show all' condition gives me the correct value.. however when I try to get the show  top 20 I'm getting incorrect values .. at any given time i need to pull the identifier that satisfies this condition if(
( Status='Missing'and DateType='CaseCreatedDate' )
OR
( [Type] = 'CC'and [RelatedEnd ] >=$(vToday)and [RelatedEnd ] <=$(v30Days))
OR
([Type] = 'PP' and [Related2End ] >=$(vToday)and [Related2End ] <=$(v30Days) )
OR ([approvals] = '0') and [Type] = 'CC' , [Identifier]

But this condition gives me incorrect top 20 records that not part of logic.

=IF(vPtNdAtt=1,aggr(if(rank(if(
( Status='Missing'and DateType='CaseCreatedDate' )
OR
( [Type] = 'CC'and [RelatedEnd ] >=$(vToday)and [RelatedEnd ] <=$(v30Days))
OR
([Type] = 'PP' and [Related2End ] >=$(vToday)and [Related2End ] <=$(v30Days) )
OR ([approvals] = '0') and [Type] = 'CC' , [Identifier]))<=20,[Identifier]), [Identifier])

[Kushal_Chawda]

@Sai78  I am still not sure how top 20 is decided ? Can you tell me which identifier should be there in top 20? Try adding all the dimensions which are used as condition in aggr function .

[Sai78]

sorry for the confusion again @Kushal_Chawda 

I need to display the top 20 based Created_Date desc ..please see attached updated application including the date column ...below top 20  identifiers, I'm expecting to see the values

PAT-44250933

PAT-44241333

PAT-44195077

PAT-S9Yx2560

PAT-S9XiCtd0

PAT-S7GnMLw0

PAT-S6p8g8L0

PAT-43318721

PAT-42095819

PAT-42092701

PAT-42089597

PAT-42062189

PAT-S2qiptk0

PAT-S2kvd0V0

PAT-RfBrlbp0

PAT-Rzegyun0

PAT-Rthm0vl0

PAT-RysF0DY0

PAT-RysQlkq0

PAT-RyKFfYP0

[Sai78]

 @sunny_talwar  @Kushal_Chawda 

 I tried the logic for single one condition by using  'only' and 'rank the logic works fine but if I try to add 'or' condition here its throwing error .. could you please help here as to how to add /OR conditions here..please see a working example for single condition .. but my requirement is to display the identifiers from these 4  conditions

=Aggr(
If(vPtNdAtt = 1,
If(Rank(Only({<[Type]= {'CC'} , [RelatedEnd ]={">=$(vToday)<=$(v30Days)"},[Identifier]>} [Identifier])) <= 20,
Only({<[Type]= {'CC'} , [RelatedEnd ]={">=$(vToday)<=$(v30Days)"},[Identifier]>} [Identifier])),
Only({<[Type]= {'CC'} , [RelatedEnd ]={">=$(vToday)<=$(v30Days)"}, [Identifier]>} [Identifier])
)
, [Identifier])

=Aggr(
If(vPtNdAtt = 1,
If(Rank(Only({<[Type]= {'PP'} , [Related2End ]={">=$(vToday)<=$(v30Days)"},[Identifier]>} [Identifier])) <= 20,
Only({<[Type]= {'PP'} , [Related2End ]={">=$(vToday)<=$(v30Days)"},[Identifier]>} [Identifier])),
Only({<[Type]= {'PP'} , [Related2End ]={">=$(vToday)<=$(v30Days)"}, [Identifier]>} [Identifier])
)
, [Identifier])

=Aggr(
If(vPtNdAtt = 1,
If(Rank(Only({<[Status]= {'Missing'} , [DateType]={"CaseCreatedDate"},[Identifier]>} [Identifier])) <= 20,
Only({<[Status]= {'Missing'} , [DateType]={"CaseCreatedDate)"},[Identifier]>} [Identifier])),
Only({<[Status]= {'Missing'} , [DateType]={"CaseCreatedDate"}, [Identifier]>} [Identifier])
)
, [Identifier])

=Aggr(
If(vPtNdAtt = 1,
If(Rank(Only({<[approvals]= {'0'} , [Type]= {'CC'}>} [Identifier])) <= 20,
Only({<[approvals]= {'0'} , [Type]= {'CC'}>} [Identifier])),
Only({<[approvals]= {'0'} , [Type]= {'CC'}>} [Identifier])
)
, [Identifier])