Post reply

Counting multiple values from a lookup

  • September 23, 2015 at 3:00 am

    #304934

    Hi all,

    I am currently trying to puzzle out how to count all of the distinct instances of yes and no from a lookup I have done.

    The lookup is =IIF(Lookup(Fields!Supplier_Code.Value,Fields!Supplier_Code.Value,Fields!First_Name.Value,"DataSet2")="","No","Yes")

    However I can't for the life of me find the right syntax to resolve this.

    Hopefully one of you will be able to help

    Thanks in advance.

  • September 28, 2015 at 6:53 am

    #1829846

    I guess there is no way of resolving this?

  • September 28, 2015 at 7:58 am

    #1829886

    david5515 (9/23/2015)

    Hi all,

    I am currently trying to puzzle out how to count all of the distinct instances of yes and no from a lookup I have done.

    The lookup is =IIF(Lookup(Fields!Supplier_Code.Value,Fields!Supplier_Code.Value,Fields!First_Name.Value,"DataSet2")="","No","Yes")

    However I can't for the life of me find the right syntax to resolve this.

    Hopefully one of you will be able to help

    Thanks in advance.

    I would suggest doing a sum in the footer. In your expression, instead of No or Yes, use 1 and 0 to count.

    example:

    ----to count the number of No values:

    =SUM(IIF(Lookup(Fields!Supplier_Code.Value,Fields!Supplier_Code.Value,Fields!First_Name.Value,"DataSet2")="",1,0))

    ----to count the number of Yes values:

    =SUM(IIF(Lookup(Fields!Supplier_Code.Value,Fields!Supplier_Code.Value,Fields!First_Name.Value,"DataSet2")="",0,1))

  • September 28, 2015 at 8:16 am

    #1829892

    Thanks Sarah,

    How can I aggregate the distinct values only?

    For example said person may appear multiple times per week, but how do I count them only once?

  • September 28, 2015 at 8:56 am

    #1829909

    Sorry, I missed the requirement to be distinct.

    Are you able to use the CountDistinct function?

    Here is the syntax: CountDistinct(expression, scope, recursive) --Recursive parameter is optional

    I think this is how the expression would be:

    =CountDistinct(Lookup(Fields!Supplier_Code.Value,Fields!Supplier_Code.Value,Fields!First_Name.Value,"DataSet2"),"DataSet2")

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply