topic Re: Problem excluding zero values from a count in QlikView

Problem excluding zero values from a count

chrisauton — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Problem excluding zero values from a count

Gysbert_Wassenaar — Wed, 07 Mar 2018 16:07:44 GMT

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]

Re: Problem excluding zero values from a count

chrisauton — Thu, 08 Mar 2018 07:49:54 GMT

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.

Re: Problem excluding zero values from a count

Gysbert_Wassenaar — Fri, 09 Mar 2018 08:47:57 GMT

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)

Re: Problem excluding zero values from a count

chrisauton — Mon, 12 Mar 2018 11:59:12 GMT

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.

Re: Problem excluding zero values from a count

sasiparupudi1 — Tue, 13 Mar 2018 16:06:05 GMT

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)

Re: Problem excluding zero values from a count

sasiparupudi1 — Tue, 13 Mar 2018 16:07:04 GMT

count(

{$<

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

>} distinct INVOICE_ID)

Re: Problem excluding zero values from a count

chrisauton — Sat, 17 Mar 2018 10:50:44 GMT

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.

Re: Problem excluding zero values from a count

Anonymous — Sun, 18 Mar 2018 22:02:20 GMT

Good evening,

you can try in the script with an field flag.

Sometimes is the best solution!

Re: Problem excluding zero values from a count

chrisauton — Mon, 19 Mar 2018 07:33:41 GMT

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

Re: Problem excluding zero values from a count

sasiparupudi1 — Mon, 19 Mar 2018 07:50:14 GMT

May be try

count(

{$<

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

>} distinct INVOICE_ID)

Re: Problem excluding zero values from a count

chrisauton — Mon, 19 Mar 2018 08:07:10 GMT

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

Re: Problem excluding zero values from a count

sasiparupudi1 — Mon, 19 Mar 2018 08:14:07 GMT

Please attach a sample

Re: Problem excluding zero values from a count

chrisauton — Fri, 23 Mar 2018 12:48:31 GMT

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.