counting with condition

[Pinkesh]

sample query result:

 

ID       level          [...]

--------------------------------

00a     level 1,      [...]

00a     level 1,      [...]

00b     level 3,      [...]

00b     level 3,      [...]

00d     level 2,      [...]

00e     level 1,      [...]

00f     level 2,      [...]

00e     level 1,      [...]

 

it should display: (PLEASE refer to the attached screenshot)

level 1: 2     level 2: 2     level 3: 1

 

So I'm trying to count the number of unique ID for each level.

 

 

My attempt:

Group them by level, then do a CountDistinct on the ID:

For level1 [=CountDistinct(IFF(Fields.level = "level 1", Fields.ID, 0)) - 1]

For level2 [=CountDistinct(IFF(Fields.level = "level 2", Fields.ID, 0)) - 1]  

-1 is for all the 'false' count

However this didnt work, it will always return 0.

 

Any advice?

 

 

[Pinkesh]

sorry forgot the image

[Pinkesh]

Cant seem to attach image:

http://imgur.com/7U6ESSI

[Katia]

Hi Pinkesh,

The described scenario can be achieved with a  item - check the attached report demonstrating the above scenario (can be opened with Standalone Designer R3 2016 and later).

The CrossTab in the sample has column groups which allow the aggregate function CountDistinct() to be executed against the set of data (all ID values in the current group).

Hope this will help.


Regards,
Katia
Telerik by Progress