Sum across a date range

[soton34]

I am really struggling with this one.

Basically I need to have a new KPI to show the distance travelled to fulfil invoices in a given date range

To do this I have linked the Location Distance to Invoice Date.

But when I use a simple SUM(IF(INVOICEDATE>=FromDate and INVOICEDATE<=ToDate,LocationDistance)) calculation then some of the figures are increasing relative to the number of lines on the invoice.

Please see attached with sample data in to see if you can help.

I'm guessing that it is something simple but I just can't see it.

Looking forward to your assistance....

Sam

[#2]

Sum({$<INVOICEDATE = {'>=FromDate<=ToDate'}>}LocationDistance)

[soton34]

Dear Felim

That's near perfect thankyou - It works brilliantly in my sample

The problem is that FromDate and ToDate are variables and when I transfer this to my master file it thinks FromDate is text rather than a variable.

How do I fix that?

[#4]

Try using $(=FromDate)

[soton34]

Looks good, will do some more work tomorrow

Fingers crossed and very many thanks for your help

[soton34]

In case anyone else picks up this discussion it was sorted out on:

http://community.qlik.com/message/240055#240055