getting a count

[#1]

hi, I have fields like BRAND, CATEGORY, LOCATION and ITEM. I want to get the count of ITEM when i selected each field. As an example, if I selected one of the BRAND then how many ITEMs are there and If I selected a BRAND and one of its CATEGORY then ITEM count should be change. how can I do this. pls help

[#2]

can I use a static box for this? I only want to get the item count.

[#3]

when i use total count with the static box for Item, then it displays N\A. why is that?

[gopalopsharma · Accepted solution]

Hello Dushan,

I assume you have a text value in ITEM field.

We can statistics only for the fields which have numeric values, but only one field at a time.

We have better ways to do it in straight table.

1. Create a cyclic group for the BRAND, CATEGORY, LOCATION

2. Insert a straight table and enter Cyclic group name in the dimension.

3. In the expression enter COUNT(DISTINCT ITEM)

It should help.

There is even one better way to do this, it is advised not to use COUNT so we can create an additional column which will have just single value 1 like below:

LOAD
     *,

     1 as CountFlag

FROM []

Then in the table above use Sum(CountFlag) and it will be much performance optimized. The only thing is that ITEM should be the most granular level in data. Since one CountFlag will refer to one ITEM.

Hope it helps..

[#5]

Thanks Gopal