Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be try
count(
{$<
INVOICE_ID={"$(=$(vE.TransparencyAmt))<>0"}
>} distinct INVOICE_ID)
Hi, thanks again, but that's returning an error.
Please attach a sample
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.