topic Sum of orders in date range using qsVariable in App Development

Sum of orders in date range using qsVariable

Mon, 20 Feb 2017 15:42:55 GMT

Hi,

I'm trying to get the sum of orders placed in a user defined date range using qsVariable but can't quite figure it out.

I'm importing data from SQL - here is a sample:

ORD_DATE	ORD_STOCKCODE	ORD_QTY	ORD_GROSS
2015-01-01 00:00:00	1234	10	£100
2016-04-01 00:00:00	4321	11	£110
2017-01-01 00:00:00	4321	5	£50
2017-04-01 00:00:00	1234	6	£50

I've set 2 variables as vStartYear & vEndYear and qsVariable allows the user to set them as any year ("2014","2015",etc).

I was assuming it would be something like: sum({$<year(ORD_DATE) = {>=$(=vStartYear) <=$(=vEndYear)}> } ORD_QTY)

but this doesn't work.

Does anyone know what I'm doing wrong - or is there a better way of doing this?

Thanks,

Keiran

Re: Sum of orders in date range using qsVariable

petter — Tue, 21 Feb 2017 07:01:08 GMT

Firstly:

You can't have anything else than a field name on the left hand side of the equal sign in a Set Expression.

Secondly:

You should put the search expression in your Set Expression inside double quotes "

Here is my suggestion for you:

Re: Sum of orders in date range using qsVariable

Tue, 21 Feb 2017 11:08:05 GMT

Hi Petter,

Thanks for your help.

Is it possible for me to get a copy of that app? I'm not sure what I'm doing wrong but it's not filtering out the date ranges, even if I put in the date range manually (e.g Sum(${<ORD_DATE={">=2016-01-01 <=2017-12-31"}>} ORD_QTY) is still including values outside that date range).

Thanks,

Keiran

*EDIT*

Looks like I added an extra $, although it still didn't work until I loaded ORD_DATE as Date(ORD_DATE, 'DD/MM/YYYY') matching my DateFormat.

So, Sum({<ORD_DATE={">=01/01/$(vStartYear) <=12/31/$(vEndYear)"}>} ORD_QTY) is working.

Thank you again for the help Petter.