topic Re: Sum across a date range in QlikView

Sum across a date range

soton34 — Thu, 14 Jun 2012 15:46:43 GMT

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

Re: Sum across a date range

Thu, 14 Jun 2012 15:49:46 GMT

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

Re: Sum across a date range

soton34 — Thu, 14 Jun 2012 15:57:33 GMT

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?

Re: Sum across a date range

Thu, 14 Jun 2012 15:58:30 GMT

Try using $(=FromDate)

Re: Sum across a date range

soton34 — Thu, 14 Jun 2012 16:01:24 GMT

Looks good, will do some more work tomorrow

Fingers crossed and very many thanks for your help

Re: Sum across a date range

soton34 — Fri, 20 Jul 2012 12:06:28 GMT

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

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