Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

sum field values across a given date range

hello,

so.. I'm tryng to sum a quantity field across a given date range

I have two variable:

vFromDate

vToDate

vFromDate is a makedate(2016,1,1) function.

vToDate is a today() function.

next I have a measure QUANT_AC that is something like this expression (that doesn't work!):

Sum({<DATA={'>$(=vFromDate)<$(=vToDate)'}>}QUANTI)

DATA is a date field from:

LOAD

  Date (Right([DAT_DOC],2) & '/' & Right(Left([DAT_DOC],6),2) & '/' & Left([DAT_DOC],4)) AS DATA

and QUANTI is the article amount.

But I get a great 0 !

PS: before that, I had this expression for the QUANT_AC measure:

SUM(If(ANNO_VENDITA=YEAR(TODAY()),QUANTI))

and it worked, but within the entire year.

what do you think?

thx

1 Solution

Accepted Solutions
shubham_singh
Partner - Creator II
Partner - Creator II

In load script use

Date#([DAT_DOC],'YYYYMMDD') AS DATA


Use a equal sign ('=') and a num function before variable definition

vFromDate is =num(makedate(2016,1,1))

vToDate is =num(today())


Then use this expression

Sum({<DATA={'>$(vFromDate) <$(vToDate)'}>}QUANTI)



View solution in original post

7 Replies
sunny_talwar

Try this:

Sum({<DATA={'>$(=Date(vFromDate))<$(=Date(vToDate))'}>}QUANTI)

shubham_singh
Partner - Creator II
Partner - Creator II

In load script use

Date#([DAT_DOC],'YYYYMMDD') AS DATA


Use a equal sign ('=') and a num function before variable definition

vFromDate is =num(makedate(2016,1,1))

vToDate is =num(today())


Then use this expression

Sum({<DATA={'>$(vFromDate) <$(vToDate)'}>}QUANTI)



Not applicable
Author

I followed your suggestions but... I give ZERO..

this is the load script piece. DATA2 is the field we are handling.

2016-12-20 10_24_39-Qlik Sense Desktop.png

This is the preview:

2016-12-20 10_25_36-Qlik Sense Desktop.png

these are our two variables: vFromDate and vToDate (don't worry about "V" capital letter, I've fixed it)

2016-12-20 10_27_04-Qlik Sense Desktop.png

this is QUANT_AC: the quantity (AC = current year) sum within a date range (vFromDate to vToDate)

2016-12-20 10_34_51-Qlik Sense Desktop.png

I give a great 0.. to all rows...

shubham_singh
Partner - Creator II
Partner - Creator II

Looks like you are using master measure and calling it a variable.

Add variables by clicking 'x' icon in bottom left corner.

Then click "create new" and put a name & expression.

Make sure you add an equal sign before variable definition.

See the part highlighted in yellow.

Date# is a dual data type function that means it will store both num and text.

Date# gives you both 20160107 and 42376.

To keep the number part use num(Date#([DAT_DOC],'YYYYMMDD')) AS DATA.

Not applicable
Author

shubham.singh‌, yes you right!

Now with the correct variable, it works better.

But.. variable vToDate show me an empty text... why?

2016-12-21 15_09_23-Qlik Sense Desktop.png

shubham_singh
Partner - Creator II
Partner - Creator II

Variable expression is correct,

show what measure have you used in showing variable in UI.

Also mark the correct answer if your matter is resolved.

EmmaMark
Contributor III
Contributor III

Sunny,

As it would be adding a variable.
Variavel brings value.

Sum({<DATA={'>$(=Date(vFromDate))<$(=Date(vToDate))'}>}vExpressao1)