Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
Try this:
Sum({<DATA={'>$(=Date(vFromDate))<$(=Date(vToDate))'}>}QUANTI)
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)
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...
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.
shubham.singh, yes you right!
Now with the correct variable, it works better.
But.. variable vToDate show me an empty text... why?
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.
Sunny,
As it would be adding a variable.
Variavel brings value.
Sum({<DATA={'>$(=Date(vFromDate))<$(=Date(vToDate))'}>}vExpressao1)