topic Previous MTD in QlikView

Previous MTD

Wed, 28 Dec 2011 23:13:08 GMT

Hi All,

I have an expression that will limit values to the previous month:

{<Date = {">=$(=monthstart(addmonths(today(),-1)))<$(=monthstart(today()))"}, Month= >}

What I would like is to modify the 'less than' portion to give me month-to-date for the previous month.

Thanks!

Previous MTD

Wed, 28 Dec 2011 23:50:53 GMT

I found this expression that works for the whole month:

{$<Month={$(=Only(Month))}>}

Much nicer syntax!

Doesn't solve my problem...

Is there a way to build up the dates with concatenation? Is there a nice way to determine what the expression is returning? I'm finding debugging to be quite difficult.

Re: Previous MTD

johnw — Wed, 28 Dec 2011 23:59:21 GMT

I'm not sure what you mean by building up dates with concatenation, but you could concatenate a list of dates and then insert them into set analysis. The below set does nothing, but should give an idea of syntax, assuming I got the syntax right.

{<Date={$(=concat(distinct chr(39) & Date & chr(39),','))}>}

For debugging set analysis, make a chart that has your set analysis expression as the first chart expression, but do NOT label the expression, and do NOT supply a caption for the chart. The caption and column heading will then show your expression. One of them (I forget which) shows the expression just like you wrote it. One of them shows it after the dollar sign substitution has taken place. Comparing the two will very often let you know what's going wrong, or at least WHERE it is going wrong.

Edit: As far as your original question goes, if I understood it...

{<Date={">=$(=monthstart(addmonths(today(),-1)))<=$(=addmonths(today(),-1))"},Month=>}

Previous MTD

Thu, 29 Dec 2011 00:36:44 GMT

I like the debugging idea! I don't rename the column when debugging, but I didn't realize the caption could help. My concatenation idea was to make up the date for the previous month with the same day as today. But the year will be a problem.

My pseudocode for my attempts is something like this:

{<Date={">=$(=monthstart(addmonths(today(),-1)))<= make a date from today() minus 30 ))"},Month=>}

I'll keep slugging away unless someone steers me in a better direction.

Previous MTD

Thu, 29 Dec 2011 00:53:08 GMT

Here is what I came up with - it seems to work!

{<Date = {">=$(=monthstart(addmonths(today(),-1)))<$(=addmonths(today()-1,-1))"}, Month= >}

I load data up to yesterday: that's why it is today()-1. The -1 in the second parameter of the addmonths function gives me last month.

I sure wish the names of the functions were more consistant...

Thanks to John for a very helpful answer - the fact that the column header, if not named, evaluates the set analysis expression is invaluable!

Previous MTD

Thu, 29 Dec 2011 01:26:04 GMT

{<Date = {">=$(=monthstart(addmonths(today(),-1)))<$(=addmonths(today(),-1))"}, Month= >}

Edit: took away today() -1 as the less than is strict.

Previous MTD

Thu, 29 Dec 2011 01:46:34 GMT

{<Date = {">=$(=monthstart(addmonths(today(),-1)))<$(=addmonths(today(),-1))"}, Month= >}