Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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]) ))
@Sai78 How about this?
=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' , [Created_Date]))<=20,[Identifier]), [Identifier])
,IF(vPtNdAtt<>1,IF(
( Status='Missing' and DateType='CaseCreatedDate' )
OR
( [Type] = 'CC'and [RelatedEnd ] >=$(vToday)and [RelatedEnd ]<=$(v30Days))
OR
([Type] = 'PAP' and [Related2End ] >=$(vToday)and [Related2End ]<=$(v30Days) )
OR ([approvals] = '0' and [Type] = 'CC') , [Identifier]) ))
Not sure, but could it be related to mis placement of Parenthesis? Not sure what your condition needs are.
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.
@Sai78 what is the measure to get the top 20?
@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)'
@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
@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])
@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 .
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 |
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])