14 Replies Latest reply: Feb 15, 2018 11:34 AM by Sunny Talwar

# How to Count and Group By

Hello World!

Please I need help to get the number of valid products (State) that a customer has on the company and group by ID

I have this table

ID -  PRODUCTS ID - STATE

15363 - VEHICLE - VALID

15363 - MOTO - INVALID

15363 - VEHICLE 2 - VALID

14321 - VEHICLE - INVALID

14321 - VEHICLE 2 - VALID

I need to get this

ID - NUMBER OF PRODUCT VALID

15363 - 2

14321 - 1

Thanks

• ###### Re: How to Count and Group By

May be this

Dimension

ID

Expression

Count({<STATE = {'VALID'}>} ID)

• ###### Re: How to Count and Group By

Sunny, hello

I have tried that. Maybe I didnt explain as well I though.

I need to get this

ID -  PRODUCTS ID - STATE - NUMBER OF PRODUCT VALID

15363 - VEHICLE - VALID - 2

15363 - MOTO - INVALID - 2

15363 - VEHICLE 2 - VALID - 2

14321 - VEHICLE - INVALID - 1

14321 - VEHICLE 2 - VALID - 1

And when I did that, I got

ID -  PRODUCTS ID - STATE - NUMBER OF PRODUCT VALID

15363 - VEHICLE - VALID - 1

15363 - MOTO - INVALID - 0

15363 - VEHICLE 2 - VALID - 1

14321 - VEHICLE - INVALID - 0

14321 - VEHICLE 2 - VALID - 1

Got it?

Sorry

• ###### Re: How to Count and Group By

Count(TOTAL <ID> {<STATE = {'VALID'}>} ID)

• ###### Re: How to Count and Group By

Thanks you very much as always.

Regards from Chile

• ###### Re: How to Count and Group By

Sunny,

I have another problem.

I got this:

ID -  PRODUCTS ID - STATE - NUMBER OF PRODUCT VALID

15363 - VEHICLE - VALID - 1

15363 - MOTO - INVALID - 0

15363 - VEHICLE 2 - VALID - 1

14321 - VEHICLE - INVALID - 0

14321 - VEHICLE 2 - VALID - 1

And when I try to filter by a list box with STATE as filter, the table shows the data without considering the filter.

Check it out.

stalwar1

• ###### Re: How to Count and Group By

Try this

Count(TOTAL <ID> {<STATE *= {'VALID'}>} ID)

• ###### Re: How to Count and Group By

Works, but when the filter goes the count is equal 0.

For example, when I filter by VALID, all the counts in INVALID goes to 0.

Withou Filter:

ID -  PRODUCTS ID - STATE - NUMBER OF PRODUCT VALID - NUMER OF PRODUCT INVALID

15363 - VEHICLE - VALID - 2 - 1

15363 - MOTO - INVALID - 2 - 1

15363 - VEHICLE 2 - VALID - 2 - 1

14321 - VEHICLE - INVALID - 1 - 1

14321 - VEHICLE 2 - VALID - 1 - 1

Filter by: INVALID

ID -  PRODUCTS ID - STATE - NUMBER OF PRODUCT VALID - NUMER OF PRODUCT INVALID

15363 - MOTO - INVALID - 0 - 1

14321 - VEHICLE - INVALID - 0 - 1

Flter by: VALID

ID -  PRODUCTS ID - STATE - NUMBER OF PRODUCT VALID - - NUMER OF PRODUCT INVALID

15363 - VEHICLE - VALID - 2 - 0

15363 - VEHICLE 2 - VALID - 2 - 0

14321 - VEHICLE 2 - VALID - 1 - 0

• ###### Re: How to Count and Group By

Try this

Count(TOTAL <ID> {<STATE = {'VALID'}>} ID) * Avg(1)

• ###### Re: How to Count and Group By

It didnt work.

The same before.

• ###### Re: How to Count and Group By

It seems to be working for me...

Without selection

INVALID Selected

VALID Selected

• ###### Re: How to Count and Group By

Something like this may.

=Count({<State={'VALID'}>}ID)

• ###### Re: How to Count and Group By

The same for Sunny.

I have tried that. Maybe I didnt explain as well I though.

I need to get this

ID -  PRODUCTS ID - STATE - NUMBER OF PRODUCT VALID

15363 - VEHICLE - VALID - 2

15363 - MOTO - INVALID - 2

15363 - VEHICLE 2 - VALID - 2

14321 - VEHICLE - INVALID - 1

14321 - VEHICLE 2 - VALID - 1

And when I did that, I got

ID -  PRODUCTS ID - STATE - NUMBER OF PRODUCT VALID

15363 - VEHICLE - VALID - 1

15363 - MOTO - INVALID - 0

15363 - VEHICLE 2 - VALID - 1

14321 - VEHICLE - INVALID - 0

14321 - VEHICLE 2 - VALID - 1

Got it?

Sorry

• ###### Re: How to Count and Group By

It Works, you rigth. Thanks you

• ###### Re: How to Count and Group By

okay nice