Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER 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
Partner
Partner

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

Try this:

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

Partner
Partner

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

Not applicable

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...

Partner
Partner

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

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

Partner
Partner

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.

Contributor II
Contributor II

Sunny,

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

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