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: 
Not applicable

ADDMONTHS problem?

Hi,

I want to make a table that has a column for every month from last year till now with the turnover in it, so I made:

Sum ({$<FACTDAT={">$(=MAKEDATE(YEAR(ADDMONTHS(NOW(2),-11)), MONTH(ADDMONTHS(NOW(2),-11))) <$(=MAKEDATE(YEAR(ADDMONTHS(NOW(2),-10,1)), MONTH(ADDMONTHS(NOW(2),-10,1)))"}>} VERKOOP1)

OR

Sum ({$<FACTDAT={">=$(=monthname ( today(), -10 )) <$(=monthname ( today(), -9 ))"}, FIXEDCAR-={"DEL"}>} VERKOOP1)

But that returns 0 for everything, while there is data.

If i replace it with

Sum ({$<FACTDAT={">$(=MAKEDATE(YEAR(NOW(2)-335), MONTH(NOW(2)-335))) <$(=MAKEDATE(YEAR(NOW(2)-305), MONTH(NOW(2)-305)))"}>} VERKOOP1)

It does work... and gives the right values...

How is this possible and what am I doing wrong... I can't use the -days because I won't know how many days there are between now and 11 months ago.

Anyone have an idea?

1 Solution

Accepted Solutions
Not applicable
Author

The set analysis is not right, it now only limits the values to smaller than today -2 months.

I think it should be:

sum ({$<

          FACTDAT={'>=$(=AddMonths(Today(), -5)) <=$(=AddMonths(Today(), -2))'}

          >}

Fact)

Then it does work, thanks for that.

Will try this tomorrow in combination with monthstart 🙂

View solution in original post

3 Replies
a_mullick
Creator III
Creator III

Hi,

The examples all give slightly different date values, which may play a part in why the 3rd example works and the others don't?

Anyway, I've attached an example that works (hopefully) with AddMonths() and I've used a different set analysis syntax, which I find easier to read (but that's just me, although I couldn't get your syntax to work, but maybe I was just having a cut and paste problem).

Hope it helps,

Azam

Not applicable
Author

The set analysis is not right, it now only limits the values to smaller than today -2 months.

I think it should be:

sum ({$<

          FACTDAT={'>=$(=AddMonths(Today(), -5)) <=$(=AddMonths(Today(), -2))'}

          >}

Fact)

Then it does work, thanks for that.

Will try this tomorrow in combination with monthstart 🙂

a_mullick
Creator III
Creator III

Yes, you're right. You correction should work (and with MonthStart() also).

Azam