Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
MVP
MVP

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

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

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

I tried that and got a null answer.

MVP
MVP

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

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

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

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

Thanks!

MVP
MVP

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

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

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

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?

MVP
MVP

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

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
Contributor III

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

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

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

Not applicable

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

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