topic Re: Easy formula error in QlikView

Easy formula error

Tue, 21 Jul 2015 16:29:14 GMT

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

Re: Easy formula error

swuehl — Tue, 21 Jul 2015 16:34:51 GMT

Try

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

Re: Easy formula error

yura_ratu — Tue, 21 Jul 2015 16:36:07 GMT

Hi Espen,

Try this

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

Re: Easy formula error

sunny_talwar — Tue, 21 Jul 2015 16:38:48 GMT

Try this may be:

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

Re: Easy formula error

Tue, 21 Jul 2015 16:40:07 GMT

Thanks! Working perfectly now 🙂