Alternate State and Percentiles

[bxprokop]

I have a Qlikview Application I am working on and am having difficulty with a couple formulas / functions.

1. In the attached application, I have a Expression Target Company that uses an Alternative State.  All Other Expressions do not.  How do I adjust the Table to only display Categories without a Null Value or Zero?

2. I can't figure out how to write a formula that will define what Percentile the Target Compay falls into (i.e. 10th, 25th.......>95th).  I have set up an Expression field for this formula.  In addition, I would like to setup Conditional formatting to highlight in yellow the Percentile the Target Company fall into.

Let me know, if and how this can be done.

Thanks in advance for your help.

[Brett_Bleess]

See if my replying will kick this back up to see if someone may be able to help given you attached a qvw file etc.  The only thing I could really do on this one is point you to the Help:

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Examples%20of%2...

Hopefully that might help to a degree in the meantime, hopefully someone else with better development skills may be able to have a look and give you some further pointers here.

Regards,
Brett

[sunny_talwar · Accepted solution]

Do you need this

[Newuser101]

Could you please put the code you used for this in the group - I can't seem to download the qvw file and I have a very similar issue

[sunny_talwar]

For the percentile calculation, I used this

If(Len(Trim(Column(2))) > 0,
	If(Column(2) <= Column(6), '10th',
		If(Column(2) <= Column(7), '25th',
			If(Column(2) <= Column(8), '40th',
				If(Column(2) <= Column(9), '50th',
					If(Column(2) <= Column(10), '60th',
						If(Column(2) <= Column(11), '75th',
							If(Column(2) <= Column(12), '80th',
								If(Column(2) <= Column(13), '85th',
									If(Column(2) <= Column(14), '90th',
										If(Column(2) <= Column(15), '95th', '>95th'))))))))))
)

For removing additional rows, I used the following if statement for all expressions expect the second one

If(Len(Trim(Column(2))) > 0, ...)