Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

Sum of orders in date range using qsVariable

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_DATEORD_STOCKCODEORD_QTYORD_GROSS
2015-01-01 00:00:00123410£100
2016-04-01 00:00:00432111£110
2017-01-01 00:00:0043215£50
2017-04-01 00:00:0012346£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

1 Solution

Accepted Solutions
MVP
MVP

Re: Sum of orders in date range using qsVariable

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:

2017-02-21 08_00_16-Qlik Sense Desktop.png

2 Replies
MVP
MVP

Re: Sum of orders in date range using qsVariable

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:

2017-02-21 08_00_16-Qlik Sense Desktop.png

Not applicable

Re: Sum of orders in date range using qsVariable

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.

Community Browser