counting with condition

4 posts, 0 answers
  1. Pinkesh
    Pinkesh avatar
    28 posts
    Member since:
    Sep 2015

    Posted 12 Jan 2017 Link to this post

    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?

     

     

  2. Pinkesh
    Pinkesh avatar
    28 posts
    Member since:
    Sep 2015

    Posted 12 Jan 2017 in reply to Pinkesh Link to this post

    sorry forgot the image
  3. Pinkesh
    Pinkesh avatar
    28 posts
    Member since:
    Sep 2015

    Posted 12 Jan 2017 in reply to Pinkesh Link to this post

    Cant seem to attach image:

    http://imgur.com/7U6ESSI

  4. Katia
    Admin
    Katia avatar
    693 posts

    Posted 13 Jan 2017 Link to this post

    Hi Pinkesh,

    The described scenario can be achieved with a CrossTab(Table,List) 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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
Back to Top