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

[Gysbert_Wassenaar]

count(

     {$<

     [Invoice Distribution Amount] = {"<0>0"}

      [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)

[Invoice Distribution Amount]

[chrisauton]

Hi Gysbert, thanks for this, but it's the value produced by vE.TransparencyAmt (a sum of several invoice distribution rows) that needs to be excluded when zero.  The zero value individual invoice distribution rows were excluded in the SQL load.

[Gysbert_Wassenaar]

You'll have to add the expression in the set definition of the other. Replace XXXX with the field of the dimension over which you calculate the vE.TransparencyAmt value.

count(

     {$<

     [XXXXX]={"=sum(

    {$<

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

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

    ,[Invoice Header Amt ABS]        ={`>=$(vL.TransparencyLevel)`}

    ,[Invoice Account]                        -=$(vL.TransparencyAcctExclusions)

    >} [Invoice Distribution Amount])<>0"}

      [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)

[chrisauton]

Thanks for this, but unfortunately it didn't do the trick.  I've managed to fix it by building the aggregated amount in the data model instead.  Not ideal, but it does work.

[sasiparupudi1]

May be try

count(

     {$<

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

      [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)

[sasiparupudi1]

Or

count(

     {$<

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

    >} distinct INVOICE_ID)

[chrisauton]

Hi Sasidhar

Thanks, but unfortunately still not working.  There must be a way to reference a variable built sum value in the set analysis statement, otherwise I can't obtain an accurate count.

[Anonymous]

Good evening,

you can try in the script with an field flag.

Sometimes is the best solution!

[chrisauton]

Hi, thanks, but can you provide more detail of your suggestion please?