Show Multiple Fields Content based on a condition

[s10157754]

Dear Qlivkiew Experts,

I have this set of database:

I would like to create a straight table to show rank1 & rank2 's value based on a condition.

My condition is: if Type is = "O" and Rank1/Rank2 is not equal to blank or "17". Then show the value from Rank1/Rank2 with the frequency of that value as well.

My current expression for the rank is: =Aggr(Only({<Type={"O"},Rank1-={"" ,"17" }>} Rank1), Rank1)

and the expression for frequency is: count({<[Type] = {"O"},Rank1-={"","17"}>} [Rank1])

But this can only return me with the value shown in Rank1 column, and I would like to have a combination of 2 fields.

My desired outcome would be sth look like below:

May I know what is the correct syntax to achieve my desired outcome?

Attached Qlikview excel file for your reference.

Best Regards

QianNing

[s10157754]

Dear Sunny,

Thank you so much for the help!

Best Regards
QianNing

[s10157754]

Dear Sunny,

Just to clarify with you, if I want to meet a few more criteria on a specific field, can i just add in simply like this?
=If(Rank <> 17 and Type= 'O', Count(If(Rank1 = Rank, Type))+Count(If(Rank2 = Rank, Type)))

Best Regards
QianNing

[sunny_talwar]

I would do this

=If(Rank <> 17,
  Count(If(Rank1 = Rank and Type= 'O', Type)) +
  Count(If(Rank2 = Rank and Type= 'O', Type))
)

[s10157754]

Dear Sunny,

Thank you so much for the help! Have a nice day!

Best Regards
QianNing