Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello community,
I'm a little bit confused about this issue, which occurs suddenly.
Since 1 month I'm working with my qlikview application and today, after a routine load the following expression doesn't work anymore:
SUM({1<DATATYPE={'ACTUAL'},BILL_YEAR={"$(=date(addmonths(today(),-1),'YYYY'))"},BILL_MONTH={"$(=date(addmonths(today(),-1),'MM'))"}>}AMOUNT)
The problem is within the set analysis - condition: BILL_MONTH={"$(=date(addmonths(today(),-1),'MM'))"}
That's how qlik depicts the values of the field in a filter-box.
My first approach was to change the datatype in the scrip like these:
NUM(BILL_MONTH,'00') AS BILL_MONTH,
NUM(BILL_MONTH,'0') AS BILL_MONTH,
NUM(BILL_MONTH) AS BILL_MONTH,
DATE(BILL_MONTH,'MM') AS BILL_MONTH,
No one of these above fixed the issue. Just one thing helped:
Changing this
SUM({1<DATATYPE={'ACTUAL'},BILL_YEAR={"$(=date(addmonths(today(),-1),'YYYY'))"},BILL_MONTH={"$(=date(addmonths(today(),-1),'MM'))"}>}AMOUNT)
into that
SUM({1<DATATYPE={'ACTUAL'},BILL_YEAR={"$(=date(addmonths(today(),-1),'YYYY'))"},BILL_MONTH={"$(=date(addmonths(today(),-1),'M'))"}>}AMOUNT)
After that all formulas were calculated correctly. But I absolutely do not want to change each formula in my application especially because there was no obvious change.
Which format opportunities do I still have? Any ideas?
Thank you in advance!
hello
i tried your formula
let a =date(addmonths(today(),-1),'MM');
trace $(a);
exit script;
and the result is 02 (not 2 as in your text box) whiwh is logical according to the format used.
just a question : did you use it last month , when the result was 01 ?
BILL_MONTH={"$(=date(addmonths(today(),-1),'M'))"}
Hello,
yes, it has worked until yesterday.
Now, I tried to build up the formula with if-condition. Result:
BILL_MONTH is defined as NUM(BILL_MONTH, '00')
This one doesn't work:
SUM(if(DATATYPE = 'ACTUAL' AND BILL_YEAR=date(addmonths(today(),-1),'YYYY') AND BILL_MONTH = date(addmonths(today(),-1),'MM') ,REVENUE))
This one does:
SUM(if(DATATYPE = 'ACTUAL' AND BILL_YEAR=year(date(addmonths(today(),-1),'YYYY')) AND BILL_MONTH = month(date(addmonths(today(),-1),'MM') ,REVENUE)))
That's strange! I didn't need to transform into year or month before. Am I able to prepare the data within the script-editor to avoid the additional year and month statements?