Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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?
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
hi i found the solution
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
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)