topic Re: Num = Month comparison bug? in QlikView

Num = Month comparison bug?

spcon2016 — Fri, 30 Mar 2018 14:56:23 GMT

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!

Re: Num = Month comparison bug?

olivierrobin — Fri, 30 Mar 2018 15:32:56 GMT

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 ?

Re: Num = Month comparison bug?

sasiparupudi1 — Fri, 30 Mar 2018 16:52:23 GMT

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

Re: Num = Month comparison bug?

spcon2016 — Fri, 30 Mar 2018 22:05:20 GMT

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?