6 Replies Latest reply: Dec 21, 2016 11:56 AM by Shubham Singh

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:

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

• Re: sum field values across a given date range

Try this:

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

• Re: sum field values across a given date range

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)

• Re: sum field values across a given date range

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

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

This is the preview:

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

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

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

• Re: sum field values across a given date range

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.

• Re: sum field values across a given date range

shubham.singh, yes you right!

Now with the correct variable, it works better.

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

• Re: sum field values across a given date range

Variable expression is correct,

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