Set analysis for the sum of the current month customers

[#1]

I have a table like this:

Month	Product	Customers
1/1/2015	Product1	412
1/1/2015	Product2	17
12/1/2014	Product1	398
12/1/2014	Product2	15
11/1/2014	Product1	375
11/1/2014	Product2	16

I want to make a aggregation that calculates the number of all customers in the most recent month of selected data. Here's what I have so far:

sum({<Month = {$(=Max(Month))}>} Customers)

In this example, I would expect it to return 429 (412+17). This instead returns a null value, and I can't figure out why.  Sorry if I missed something obvious.

[#2]

bommaramcharan,

Thank you for the additional context.  I have read similar set analysis overviews, and they are always insightful.  I like the power of set analysis, though I don't always get it right in practice.  Are you suggesting that I use a specific part of this explaination in my solution?

I tried the test.qvw you attached, however it seems identical to the one uploaded by Arul Settu.  Am I missing something, or did you mean to attach a different qvw file?

[PrashantSangle]

Hi,

Then also use Date()

Try like,

sum({<Month = {$(=Date(Max(num(Month))))}>} Customers),

1st check in text object

=date(Max(num(Month)))          //Format as per your month date format

Regards

[arulsettu]

hi i found the solution

[#5]

Try creating 3 additional columns

Month(Month) as mon,
year(Month) as yr,
num(Month(Month)) as monthnum,

and use the following set analysis

=Sum({$ <yr={$(=max(yr))}, monthnum={$(=max(mon))} > } Customers)

-Qlikuser

[#6 · Accepted solution]

It's working!  Thanks for all your help everyone!

I tried all the suggestions you sent in last night, but I couldn't get them to work.  However, it gave me an idea that solved it.

For those who have a similar issue, here's what wound up working:

I added the following line to my load script.

num(year(Month))*100+num(month(Month)) as year_month_num

This gave me a number for each month like 201412, 201501, 201502, etc., with the most recent month having the highest number.

Then in my chart, I used the following expression.

Sum({<year_month_num = {$(=Max(year_month_num))}>} Customers)