Easy formula error

[#1]

Hi all,

So I am coming across a problem that I am pretty sure has a really simple fix. I have a Straight Table that is listing vendors with total spend towards that vendor. These are based on invoices with a number of lines and I am only wanting it to count the lines where there is a value in the project field (called POSID).

My straight table:

My calculation:

Sum ({<BLART={'K1','K2','K3','K4','KR','KA'}>}#ProjActualNetSpend_NOK)

I tried the expression below, but it only gave me very wrong values. Does anyone have any other suggestions?

Sum ({<BLART={'K1','K2','K3','K4','KR','KA'}, POSID={'<>0'}>}#ProjActualNetSpend_NOK)

For further illustration, what I need is basically only the lines circled in red to be counted, whereas it is counting all lines. So that for example the 224 at the top is counted twice (both for row 1 and for row2).

[swuehl · Accepted solution]

Try

Sum ({<BLART={'K1','K2','K3','K4','KR','KA'}, POSID={"=len(trim(POSID))>0"}>} #ProjActualNetSpend_NOK)

[yura_ratu]

Hi Espen,

Try this

Sum ({<BLART={'K1','K2','K3','K4','KR','KA'}, POSID={'$(=len(POSID)>0)'}>}#ProjActualNetSpend_NOK)

[sunny_talwar]

Try this may be:

Sum ({<BLART={'K1','K2','K3','K4','KR','KA'}, POSID={'*?'}>}#ProjActualNetSpend_NOK)

[#5]

Thanks! Working perfectly now 🙂