on 2006 Aug 09 2:55 PM
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?
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..........
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
Hi Ashmith,
Have you seen this doc:
It might help you...
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
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.
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.
User | Count |
---|---|
66 | |
10 | |
10 | |
10 | |
10 | |
8 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.