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

Set Analysis monthstart(today()) does not work in January

I am currently using these formulas to pull the current month and previous mtd.  For January I am getting some crazy numbers. 

Current Mtd:

sum({<Tran Date]={">=$(=monthstart(today()))"}>}Fees)

Previous month to date:

sum({<Tran Date]={">=$(=monthstart(max(trandate),-1) & '<=' & Addmonths(max(trandate),-1))"}>}Fees)

If someone could help me adjust these formulas to pull correctly, I would greatly appreciate assistance.

Thanks,

Kristy

13 Replies
swuehl
MVP
MVP

First, try without the & '<=' &, with an additional $ sign expansion and some brackets fixed:

sum({<Tran Date]={">=$(=monthstart(max(trandate),-1))<=$(=Addmonths(max(trandate),-1))"}>} Fees)

Not applicable
Author

I tried that and got a null answer.

swuehl
MVP
MVP

Ok, I noticed that there might be an opening [ missing on [Tran Date] ?

Then, is this the same field you are calculating the max on? Then you need also max([Tran Date]).

Finally, you might need to check the Format of the comparison between field [Tran Date] and your set element list you get back from your search expression. Date format must match!

Or use numerical representation instead:

=sum({< [Tran Date] = {">=$(=num(monthstart(max([Tran Date]),-1)))<=$(=num(Addmonths(max([Tran Date]),-1)))"} >} Fees)

Hope this helps,

Stefan

Not applicable
Author

I think that fixed the previous mtd, but I am still out for the current mtd.

Thanks!

swuehl
MVP
MVP

Also probably some format issues with comparing your [Tran Date] field and your search expression result? Try

sum( {<Tran Date]={">=$(=num(monthstart(today())))"}>} Fees)

Have a nice evening,

Stefan

Not applicable
Author

This is giving me a 0 result. If I use my existing formula, I get a very high result. (250k instead of 50k) Could this be because I have 2 January’s listed, 2011 and 2012?

swuehl
MVP
MVP

It should work quite nicely with multiple years.


But again, I missed the opening square bracket:

sum( {< [Tran Date]={">=$(=num(monthstart(today())))"}>} Fees)

[Tran Date] field is of recognized date type, with a numerical representation?

Hm, maybe the num() is not a good idea here, try

sum( {< [Tran Date]={">=$(=Date(monthstart(today())))"}>} Fees)

your standard date format should match the [Tran Date] format.

Hope this helps,

Stefan


jduenyas
Specialist
Specialist

Try this in a text object to test the function:  =monthstrat(today())

It should return, of course, 1/1/12

Not applicable
Author

I didn’t even think to do that. Why in the world would it be 12/1/11?!