Problem excluding zero values from a count

[chrisauton]

Hi, I have an expression for a sum amount (vE.TransparencyAmt) that works fine and returns the expected results.  I also have an expression (vE.TransparencyCount) that is designed to count the distinct number of invoices included in vE.TransparencyAmt.  This works ok, except that zero values that are the result of vE.TransparencyAmt need to be excluded, but are being included in the count.

vE.TransparencyAmt

sum(

    {$<

    [Calendar Rolling Two Years]    ={'1'}

    ,[Date Type]                                ={'Invoice GL Date'}

    ,[Invoice Header Amt ABS]        ={">=$(vL.TransparencyLevel)"}              //a variable to store a user defined amount

    ,[Invoice Account]                        -=$(vL.TransparencyAcctExclusions)    //a list of excluded account codes

    >} [Invoice Distribution Amount])

vE.TransparencyCount

count(

     {$<

      [Calendar Rolling Two Years]    ={'1'}

    ,[Date Type]                                ={'Invoice GL Date'}

    ,[Invoice Header Amt ABS]        ={">=$(vL.TransparencyLevel)"}              //a variable to store a user defined amount

    ,[Invoice Account]                        -=$(vL.TransparencyAcctExclusions)    //a list of excluded account codes

    >} distinct INVOICE_ID)

I've tried several ways of including vE.TransparencyAmt in the set analysis for vE.TransparencyCount, but I always get an error.  Is it even possible to do that?  Or should I be using some other approach?

I'm fairly new to Qlik, so any help much appreciated.

Thanks

Chris

[sasiparupudi1]

May be try

count(

     {$<

INVOICE_ID={"$(=$(vE.TransparencyAmt))<>0"}

    >} distinct INVOICE_ID)

[chrisauton]

Hi, thanks again, but that's returning an error.

[sasiparupudi1]

Please attach a sample

[chrisauton · Accepted solution]

For the record I've now resolved this by creating an additional variable: -

vE.PaidDateInvAmtNotZeroFlag - If($(vE.TransparencyAmt)<>0,1,0)  to create a flag  of 1 for non zero amounts

Then used this variable in the expression:-

sum(aggr($(vE.PaidDateInvAmtNotZeroFlag),PO_HEADER_ID))

Thanks for all the helpful suggestions everyone.