Exclude count where the amount is zero

[Former Member]

Good morning

I need assistance in the following please?

I have a query where I display the Top 20 expenses (amounts) and the number of records (occurrences) for those expenses.

The number of records is a count of all the credit note numbers created for the expenses (lowest level).

I am also excluding the 0.00 amounts from the query with a condition.

I need to exclude the count where the amount = 0.00 as well, but I cannot get that to work. It still counts all the occurrences no matter if the amount is o.oo or not.

Please assist?

Thanks

Cj

[Former Member]

0.00 amounts were excluded in the DTP filter. DOne

[Former Member]

How do you perform the couting ?

Have you tried the "Counter of all detailed value <> zero" under exception aggregation?

- Danny

[Former Member]

1. right click the key figure (Amount) and select properties

2. Expand the drop down box "Calculate results as"

3. Select All values <> 0.

or you can insert a new fomula, Use data function "Counter" to count the key figure (It returns 1 when <> 0, and 0 when =0).

[Former Member]

Hi,

Have you checked the condition mentioned in Query Designer.

Can you let us know how you have put condition on amount.

When you do condition on amount NE 0 then it will restrict the entire record.

Have you tried with formulas.

Reg

Pra

[Former Member]

Hi there,

Thanks for your response.

I need to display the top20 expenses per Credit note number" excluding o.oo amounts and excluding the count for those instances where the amount for "Credit note number" is 0.00.

Problem is, is that the number of records KF counts the amount of 'Credit Note numbers', not the amount of 'Damage Codes'.

Condition1 : on 'Credit Note number' where COST > '0'

Condition2 : Top 20 on 'Credit note number'

Only the 'damage code', the 'count' and 'expense' amounts should be displayed in the query results.

For 'damage code' 09301 (which has different 'credit note numbers, not displayed), there should be 14 records where the amount is > 0.00. The total amount = x, which is correct in the results, but the count in result still says 19 records, the original amount of records counted per 'credit note number'.

for the last reply received....""Counter of all detailed value zero" under exception aggregation?" .... How do I do this?

Thanks, yet again.

[Former Member]

Hi,

Result will not work on Conditions. It will supress the record not the result.

One thing you can do is put ref char for that keyfigure.

Let us know whether it works or not.

Reg

Pra

[Former Member]

If you want to ignore the records with cost 0 then create a formula as follows:

(COST <> 0)*1 + 0,

This will generate 1 if cost is not ZERO and 0 if cost is ZERO.

Go to Aggregation tab and select the Exception Aggregation as "Counter for all detailed value <> 0" and set the referenc char as credit not number.

- Danny

[Former Member]

Danny, thanks for the reply.

I do not have time to change the infoObject and transport to production. Business wants this urgently. Is there a way I can do it in BEX directly?

Thanks

Cj