Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
So I am changing a the date on a variable I created in the Variable Overview.
However when I do this my formula stops working and doesn't give me what I want.
Variables are
vMonth = 01/12/2017
vPreviousMonth = 01/11/2017
and my formula to return the number of entries that are in 12/2017 is
=num(sum(if(month.1=vMonth,1,0)),'#,##0')
and for 11/2017:
=num(sum(if(month.1=vPreviousMonth,1,0)),'#,##0')
Any ideas as to why its not working would be greatly appreciated.
Thanks
try that
=num(sum(if(month.1=$(vMonth),1,0)),'#,##0')
No that didn't work unfortunately.
I created them in the script now like this:
handlerinfoqvd:
LOAD Team, month,
key1
from $(QVDPath)handlerinfo.qvd (qvd);
Months:
load max(month) as vMonth,
(max(month)-1) as vPreviousMonth
from $(QVDPath)handlerinfo.qvd (qvd);
vMonth is working however vPreviousMonth isn't.
How do the values in your month field look like?
This is handlerinfo.qvd table
So your month field is actually having date values. Try like:
MonthStart(max(month) , -1) as vPreviousMonth
No that doesn't seem to work for me but thanks.
I also tried the below but no luck.
handlerinfoqvd:
LOAD Team, month,
key1
from $(QVDPath)handlerinfo.qvd (qvd);
Months:
load max(month) as vMonth,
if(right(max(month),2)='01',max(month)-89,max(month)-1) as vPreviousMonth
from $(QVDPath)handlerinfo.qvd (qvd);
What is the output you are getting using the statement I suggested? Could you share your qvd with sample data?
The monthstart worked.
Thank you very much.