Selecting possible month

[#1]

Hey,

I am trying to write an expression that counts all possible distinct users in any given month and year. I want to use set analysis so that if the user has no filter apply it automatically defaults to the latest month and year. Unfortunately, I can't seems to get the month part to work. So far I have:

count({<Year={$(=Max(Year))},Month={$(=Max(Month))}>} Distinct uname)

The problem that I am having is that the set analysis is triggering Year = 2009 and month = 12, though I want month to be 11. Any ideas?

Many Thanks in advance

[Anonymous · Accepted solution]

Are you sure that you do not have any dirty data where month is 12?

The same of course also goes for any prior years to 2009 with month 12.

Your function picks the highest month, disregarding the year.

Normally I find adding Year&Month to a new field YearMonth quite valuable.

E.g. as Year*100+Month to get 200901, 200909, 200910 etc.

Then you should be able to runt MAX(YearMonth) and get the result you want.

[johnw]

(Edit: Got beaten to it. )

Max(Month) will give you the maximum month in the whole data set, not just in 2009. You can apply set analysis to THAT expression to get the maximum month inside of the maximum year:

count({<Year={$(=max(Year))},Month={$(=max({<Year={$(=max(Year))}>}Month)}>} distinct uname)

You could alternatively define your month to include the year, or add a new field with that definition. I typically define months as date(monthstart(Date),'MMM YY'). I see a lot of people calling this field the "Month Year" or "Year Month". That simplifies things:

count({<"Month Year"={'$(=max("Month Year"))'}>} distinct uname)