Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
spcon2016
Partner - Contributor III
Partner - Contributor III

Num = Month comparison bug?

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'))"}


months.JPG

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!

3 Replies
olivierrobin
Specialist III
Specialist III

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 ?

sasiparupudi1
Master III
Master III

BILL_MONTH={"$(=date(addmonths(today(),-1),'M'))"}

spcon2016
Partner - Contributor III
Partner - Contributor III
Author

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?