Skip to main content

App Development

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

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
petter
Partner - Champion III
Partner - Champion III

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

View solution in original post

2 Replies
petter
Partner - Champion III
Partner - Champion III

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
Author

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.