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

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

Wed, 04 Jan 2012 13:33:42 GMT

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

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

swuehl — Wed, 04 Jan 2012 14:06:52 GMT

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)

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

Wed, 04 Jan 2012 15:04:29 GMT

I tried that and got a null answer.

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

swuehl — Wed, 04 Jan 2012 15:12:49 GMT

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

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

Wed, 04 Jan 2012 16:14:57 GMT

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

Thanks!

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

swuehl — Wed, 04 Jan 2012 16:37:32 GMT

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

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

Wed, 04 Jan 2012 16:46:47 GMT

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?

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

swuehl — Wed, 04 Jan 2012 17:02:41 GMT

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

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

jduenyas — Wed, 04 Jan 2012 17:16:59 GMT

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

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

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

Wed, 04 Jan 2012 17:43:16 GMT

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

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

jduenyas — Wed, 04 Jan 2012 17:45:45 GMT

Check your systems calendar/date.

Your are still living in last year. 🙂

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

swuehl — Wed, 04 Jan 2012 21:47:06 GMT

Why in the world would it be 12/1/11?!

Another possibility:

today() is equivalent to today(2), which means: Date when the document was opened

I assumed that you have opened the document after Jan 1st, but maybe you have still kept it open since last December?

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

jduenyas — Wed, 04 Jan 2012 21:51:38 GMT

Brilliant!

Now I understand why I do not get good data when I keep my application open over night or over a weekend.

Thanks a lot.

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

Wed, 04 Jan 2012 23:32:55 GMT

HAHA! That is why it says today=12/15. It stays open on the server.

Thank you so much,

Kristy