Condition - Bottom 5 values with zero suppres

[Private_Member_24235]

Dear all,

I have built a query in which I want to apply a condition “Bottom N” to provide only the Bottom 5 entries.

Due to the fact, that the query result provides also lines with zero/no value I want to exclude them first with a second condition “not equals 0”, but the Bottom condition seems to considers the suppressed lines as well.

E.g. I have 20 records of which 5 have zero value. When I apply the “Bottom 5” condition and the “not equals 0” together, the query returns no result. Although I have suppressed the zero lines, the Bottom N condition counts these 5 records.

Is there any other way to provide the Bottom N lines in a query without considering the lines which has 0?

Any idea would be great. Thank you in advance.

Best regards

Stefanos from Munich/Germany

[cornelia_lezoch · Accepted solution]

Hi Stefanos,

what happens if you put both condition-definitions into one condition? (right now you have defined two conditions idividual, but you could merge them.

If this does not help, I would probably create a new calculated key figure, which takes over from the "PO Cov. achieved" in cases where it is not 0 and make a very big number in cases where the "PO..." equals 0.

Then let your "bottom 5" condition run against this calculated key figure.

regards

Cornelia (right now in Munich )

[BernhardE]

Hi Stefanos,

This scenario is a bit tricky:

- Conditions and Zero Suppression are both display functionalities. Conditions are evalutated in first place, so using Zero Suppression will result in hiding bottom N 0-values.
- Conditions don't have any priorizations and are evaluated independent from each another so your above approach will not work either.

You could try the following workaround:
-> Create a hidden formula in which you manipulate the zero values (eg KFG/KFG*KFG to turn 0-values into NDIV0 errors)
-> Now you could apply the botton N condition on the hidden formula.

Maybe you can check if this approach helps for your scenario.

Best regards,
Bernhard

[Former Member]

Hi,

Instead of suppressing 0 lines using the condition , you can use the function from query designer to suppress columns or rows with 0 values (available in properties when you open query from query designer).

After that you can use the condition as per your requirement.

Regards,

Amit