how to count the value in webi report?

[Former Member]

Hi All,

i am stuck on the problem it seems simple but i am unable to resolve it.

i have three columns 1. Service Number  2. Amount  3.Date. the report looks like

Service No     Amount     Date    

1234               55               1/2/10

1234               85               1/2/08

1234               65               1/2/10              

1235               74               1/2/06

1235               55               1/2/08

1236               65               1/2/09

i further filter this to get the data based on the most recent date. now the table looks like

Service No     Amount     Date    

1234               55               1/2/10

1234               65               1/2/10              

1235               55               1/2/08

1236               65               1/2/09

But now i have to create another column named "Count" which should show the count value of all Service No such that

Service No     Amount     Date            Count

1234               55               1/2/10     3

1234               65               1/2/10     3         

1235               55               1/2/08     2

1236               65               1/2/09     1

i tried to get the result by using the Formula =Count ( [ ServiceNO ] ; All )

but i was unable to get the desired result.

How can i solve this?

Thanks

[Former Member]

Hi Malik

As this is your table:- 

Service No         Amount       Date   

1234                       55            1/2/10

1234                       85            1/2/08

1234                       65            1/2/10             

1235                       74            1/2/06

1235                       55            1/2/08

1236                       65            1/2/09

You want to count for unique Service No but the table you showed below for the output its not possible to count on this below table, because for the Service No (1234) it will always count separately for each row.

The count is possible if you remove the Amount ,

Service No     Amount        Date            Count

1234                  55               1/2/10               3

1234                  65               1/2/10               3        

1235                  55               1/2/08               2

1236                  65               1/2/09               1

if you will remove Amount the WEBI will show the Output table like

Service No      Date           

1234                 1/2/10    

                          1/2/10            

1235                 1/2/06

                          1/2/08

1236                 1/2/09    

So you can see there is the format of the table , now you can use the formulae

Count(Service No);all

It will give you the proper output

[jyothirmayee_s]

Hi,

You are counting on SErvice NO. and trying to display the count on same level?.

In your exmaple:

Service No. 1234  has 2 different value 55 and 65 and then counting on same level.

Reomove the service no and then you will see the total Amount and the count of Service No.

What is the result you wanted to see though?.

Thanks,

Jothi

[Former Member]

Hi Jothi,

Actually i want to see result

Actually i had a table where i have hide the repeating value of Service number, Now i have to create a column which should count the Total value of Service number.   example my previous table was like this

Service Number    Amount    Date                   Count

1234                        85          1/1/2012               1

1234                        55          1/1/2011               1

1234                        88          1/1/2010               1

1231                        11          1/1/2012               1

1231                         2          1/1/2011               1

1241                         85         1/1/2012               1

them i hide the duplicate values and table looked like this where i want to Count even the hidden values of service number. it should look like this :

Service Number    Amount    Date                   Count    

1234                        85          1/1/2012               3

1231                        11          1/1/2012               2

1241                         85         1/1/2012               1

Thanks

[Former Member]

Create a Measure Object using Count(Service No) In(Date) This should give you the expected value. One thing i noticed your amount is not aggregating is that a dimension column or measure.

Hope this is helpful

[Former Member]

Hi Tauseef,

Count(Service No) In(Date) formula is not working for me i am getting 0 in count column.   My amount is not aggregating because i was suppose to show the data base on most recent date and i was not suppose to add the sum .   Only problem i am facing is to resolve Count Problem.   I only have to Count the Service Number even the one those were hidden.

Thanks

[jyothirmayee_s]

Hi,

So you wanted to display the amount of a maximun date.

Can you create a variable using

Service number where date =  Max(Date)

insert this formula and the Count.

Let me know if this works.

Thanks,

Jothi

[former_member188911]

First of all get the guide Using Formulas and Calculations from http://help.sap.com/boall_en

for the version you are using. You must look for the chapter regarding Calculation Contexts, use the samples provided. If doesn't work please tell us the version and patch level because there have been bugs we have fixed and you may be stuck because of something that was a defect and is now solved

Thanks!

[Former Member]

Hi Jothi,

i just want to count the Service Number, Even the duplicate entries of the service number that were later hidden.

Thanks