How to Count and Group By

[jaarroyl]

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

[sunny_talwar]

May be this

Dimension

ID

Expression

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

[nithin_miryala]

Something like this may.

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

[jaarroyl]

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

[jaarroyl]

The same for Sunny.

thanks for your help

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

[sunny_talwar · Accepted solution]

Try this as your expression

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

[jaarroyl]

Thanks you very much as always.

Regards from Chile

[jaarroyl]

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‌

[sunny_talwar]

Try this

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

[jaarroyl]

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