Count using a Key Figure

[Former Member]

Hi All,

I have an issue using COUNT in a query. The issue is that I have to count the no. of material based on a certain KF, say sales. If the sales amount or value is non-zero then the material has a count of 1. If the sales amount is zero then the count is 0.

Now, I need to display the no.of materials in a particular period with sales in that period. Could you please let me know how is this possible?

[Former Member · Accepted solution]

Hi,

Create a RKF in which you can restrict with material and sales. In another CKF you can write a formula to check whether it is eual to zero or not and use the count function to count the records with no zero.

Else create a condition to filter sales with zero values and in the columns, in the new formula use count function for sales.

Hope this helps you..........

[Former Member]

Hi,

create formula for count in row

IF sales <> 0 THEN 1 ELSE 0

now restrict to a certain period and place material in rows. Result row will show the number of materials with sales in the selected period in column for your fomula.

Best regards,

Björn

[Former Member]

Hi Bjorn,

If I do it that way, don't I need to display the individual materials in the query? I don't want to display the individual materials in the query, all I want to do is display the no. of materials that had sales in a paricular period. And the only way for me to do this count of materials is to look into the Sales KF and see whether it has a non-zero amount in it.

Please advise.

Thanks,

Ashmith Roy

[Former Member]

Hi,

try to hide material in query designer, so only result row remains. Values in result row should persist unchanged. In addition you could add time period to columns to display counter for more than one period.

Best regard,

Björn

[Former Member]

Hi Bjorn,

When I set the Maeterial to 'No Display', I am still getting individual rows for all the material in addition to the overall result row. For example:

Count of Material 1

Sales Amount 10

Count of Material 1

Sales Amount 30

So on and so forth...

In addition I get the overall Result row,

Count of Material 2

Sales Amount 40.

Please advise as to how to display just the overall result and not the individual rows.

[former_member188975]

Hi Ashmith,

Have you seen this doc:

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/7e58e690-0201-0010-fd85-a2f...

It might help you...

[Former Member]

You are right, I tried myself and option "hide" only suppresses the first column with values for the corresponding characteristic...

I am sorry, but I have no idea - maybe someone else can propose a way to limit output to result row? I suppose this might be difficult because it is necessary to keep material in rows - you can only decide per material whether there were sales or not.

Best regards,

Björn

[Former Member]

Bjorn,

Thanks for the input anyway. I will let you know if I find a way to do this.

Thanks,

Ashmith Roy

[Former Member]

Hi Ashmith,

Create a calculated Key figure like..

COUNT ( SALES > 0 )

Count is the first function under data functions.

And use it in your query.

This should solve your problem

Ashish.

[Former Member]

Hi Bhanu,

I am aware of the document that you mention. But the difference here is that the only way I can count the no. of materials is by looking at the value in the Sales KF. If the Sales amount is non-zero then Material Count has a vlaue of 1, if not it is 0.

I am not counting the materials in relation to another characteristic.

[Former Member]

Hi Ashish,

That is how I have it. The COUNT data function does give me 1 when sales is > 0. But how do I do a count of materials in a given fiscal month with sales > 0?

[Former Member]

Do u have the coulumn as a formula or have it as a calculated key figure?

are you running the report only for a month at a time?

Please let me know..

Ashish.

[laurence_hogard]

What if the purpose is to have the material count without adding the Materials itself in the rows?

Then it looks like this solution is not working.

I created a Count KF based on a formula variable. This works but when applying a BEx condition on another key figure, the count result is not changing along with the filtering that happened because of the condition.regards,

Laurence.