Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Friends,
I have created following variable in Qv document
let vTdate=num(Date(MakeDate(2019,10,31),'YYYY/MM/DD'));
and I have written following expression in my pivot table and it works perfectly
SUM({<FYYYYMM={$(=vTmonth)},POL_TYPE={'R','A'},HISTORY={'Y'}>}PREMIUM)
but when I try to change the expression as shown below it does not work
SUM({<FYYYYMM={"<=$(vTmonth)"},POL_TYPE={'R','A'},HISTORY={'Y'}>}PREMIUM)
I want to change the equal to less than or equal. Pls help me to correct the expression
this is the problem
text(DATE(POL_PERIOD_FROM,'YYYYMM'))AS FYYYYMM
creates text, text cannot be evaluated using > or <, hence it worked with only =
first expression should work in my opinion if you make FYYYYMM as num
something like this
num#(text(DATE(POL_PERIOD_FROM,'YYYYMM'))) AS FYYYYMM
maybe try this
SUM({<FYYYYMM={"<=$(=vTmonth)"},POL_TYPE={'R','A'},HISTORY={'Y'}>}PREMIUM)
Thanks for your reply,
But it does not work
Very Sorry
I have missed to mention My expression for vTmonth . It is as follows
LET vFmonth=Date($(vFdate),'YYYYMM')
check if this works.
SUM({<FYYYYMM={"<='$(vTmonth)'"},POL_TYPE={'R','A'},HISTORY={'Y'}>}PREMIUM)
if not can you check at bottom pane of expression dialog and let me know what this expression is evaluated to?
Dear Asinha
It is still not working and expression evaluation is like below
is FYYYYMM a date field? or a number field?
can you see what is evaluates to?
SUM({<FYYYYMM={"<=$(vTmonth)"},POL_TYPE={'R','A'},HISTORY={'Y'}>}PREMIUM)
also try
SUM({<FYYYYMM={"<=num#('$(vTmonth)')"},POL_TYPE={'R','A'},HISTORY={'Y'}>}PREMIUM)
Both Expression does not work
FYYYYMM is created in my master calendar with the syntax
text(DATE(POL_PERIOD_FROM,'YYYYMM'))AS FYYYYMM
Evaluation for 1st Expression
Evaluation for 2nd Expression
this is the problem
text(DATE(POL_PERIOD_FROM,'YYYYMM'))AS FYYYYMM
creates text, text cannot be evaluated using > or <, hence it worked with only =
first expression should work in my opinion if you make FYYYYMM as num
something like this
num#(text(DATE(POL_PERIOD_FROM,'YYYYMM'))) AS FYYYYMM
Thanks
It is working. I was not aware that text fields does not work with <> signs. I learned it from you. Thanks you very much. The other this I am not very familiar with these date functions. Can u pls recommend some documents to read.