Create an expression for the calculation of total count of the top 6 values

[sweens78]

Hi guys,

I'm looking to make an expression of the total count of 6 of top 6 values if that makes sense my expression is

Count(if([PatientsDischargedFromED_ID]='1',NEWKEYID)) this returns back the total where I just want to total count of the top 6 values.

Thanks  Martin

[giovanneb]

Hi,  use setanalysis try this

Count ({< [PatientsDischargedFromED_ID] = {'1'} >}  NEWKEYID)

if you need to pass more parameters in the analysis follows

Count ({< [PatientsDischargedFromED_ID] = {'1', '2', '3', '4', '5', '6'} >}  NEWKEYID)

Best Reguards

[vishsaggi]

may be this if you using Pivot table?

CalculatedDimension

= Aggr(If(Rank(Count({<[PatientsDischargedFromED_ID]= {'1'} >} NEWKEYID)<7, NEWKEYID), NEWKEYID)

Expr:

= Count({<[PatientsDischargedFromED_ID]= {'1'} >} NEWKEYID)

If you using Straight table use Dimension Limits tab then show largest 6 values for your Dimension NewKeyID.

[sweens78]

Hi Vishwarath,

the dimension expression = Aggr(If(Rank(Count({<[PatientsDischargedFromED_ID]= {'1'} >} NEWKEYID)<7, NEWKEYID), NEWKEYID) is giving an error at the bracket just before the 'If' statement, any ideas ?

Thanks a mill

Martin

[dx_anupam]

Hi Martin,

you can use Dimension limit to achieve the same.

[sweens78]

Hi Anupam

I know but its in a KPI I need it if that makes sense  so it would be an expression in a text box.

[dx_anupam]

Hi Martin,

Please find below formula for getting TOP 6 result in textbox

=Concat(DISTINCT {<[State]={"=Rank(Sum(Sales))<7"}>} [State], ', '&chr(10))

this need to modify as per your data.

If you provide a sample data .xls, I will give a try.

I think the fields you have to arrange as below

Concat(DISTINCT {<[NewKeyID]={"=Rank(Count(NewKEYID))<7"},[PatientsDischargedFromED_ID]={1}>} [NewKEYID], ', '&chr(10))

Regards,

Anupam

[Anonymous]

Hi Martin,

try this

Count({<[PatientsDischargedFromED_ID]={"=rank(Count(NEWKEYID))<7"}>}NEWKEYID)

[vishsaggi]

‌MAy be I missed a parenthesis  like

=Aggr(If(Rank(Count({<[PatientsDischargedFromED_ID]= {'1'} >} NEWKEYID))<7, NEWKEYID), NEWKEYID)

if you want to display this in text box use

= concat(copy paste above expr her, chr(13))