I am hoping someone can help me figure this out as it is confusing and frustrating me.
I have a pie chart and a bar chart, both of them should use the most recent month's data, so for the period under review, we use Dec-2010.
In my pie chart, I use this expression to get a full count of distinct customers.
Expression 1
count ({<[BUSINESS DATE Month-Year]= {"= date(max(BUSINESS_DATE),'MMM-YYYY')"}>}distinct CUSTOMER_NO)
This pie chart only has one dimension called Status.
The result of the count I do does not give me the value for the most recent month value, but instead counts all the distinct customer numbers worth of data that I have, which is totally incorrect
and that expression gives me the right value for distinct customers in Dec 2010.
Can someone help me out with expression 1, so that both expression 1 and expression 2 evaluate to the same answer, as I cannot go in each month to change the month year value to correspond to the month being looked at
count ({<[BUSINESS DATE Month-Year]= {"=[BUSINESS DATE Month-Year]= date(max(BUSINESS_DATE),'MMM-YYYY')"}>}distinct CUSTOMER_NO)
The trick is to test the search expression in a Business Date Month-Year list box to see if it filters correctly: =[BUSINESS DATE Month-Year]= date(max(BUSINESS_DATE),'MMM-YYYY')
If no value correspondes to your search then all values will be selected and that is why you get a number that is a total count of your customers.
The following expression may work, too.
count ({<[BUSINESS DATE Month-Year]= {'$(=date(max(BUSINESS_DATE),'MMM-YYYY'))'}>} distinct CUSTOMER_NO)
Thanks Karl, I will have to try that out and see what happens.
Just out of curiousity, can you tell me why in the expression I have to reference the Business date Month Year twice and not once like I have been doing?
zebadguy wrote:Just out of curiousity, can you tell me why in the expression I have to reference the Business date Month Year twice and not once like I have been doing?
Use the second expression instead. There's no reason to search for a value you already know.
But in regards to the first expression and why it repeats the field, it's using a search expression (designated by the double quotes instead of single quotes). When you specify a search expression that starts with an equals sign, you can do so by using any expression that evaluates to true (non-zero) for the row(s) you want. So this expression checks for the condition of your month year being the desired month year. If it equals, it returns true (-1), else false (0). But that's a complicated way to solve the problem.
Good question. I don't know, but when you search by an expression you put the first = to tell the search engine that it is an expression and then you put a complete condition statement that returns true and false. For example,
Hi Karl, Thanks for all your help, but I have tried the code you suggested in your first reply and I am still getting all the values, instead of getting just the value for Dec 2010.
Is there anything else you can suggest to help me out?
A common problem in set analysis is matching the date format between the field and the expression, so I would confirm that =date(max(BUSINESS_DATE),'MMM-YYYY') return a value in the same format as the values in [BUSINESS DATE Month-Year] column.
I don't know why set analysis just doesn't convert dates to numbers automatically to avoid this problem which probably accounts for 25% of the problems users have with set analysis. Supposedly using the pound sign (#) before the equal sign (eg. $(#=max(BUSINESS_DATE)) converts the expression to a number, but I haven't had much success making that work. I need to play around with it more.
In the meantime you should always make sure the date formats match when evaluating dates in set analysis.
If you can upload a same sample of your file to test it.
Karl Pover wrote:I don't know why set analysis just doesn't convert dates to numbers automatically to avoid this problem which probably accounts for 25% of the problems users have with set analysis.
I'd like to see it fixed, but I do think I understand why it behaves the way it does.
Set analysis behaves just like making selections, and as best I understand from a discussion with QlikTech, actually reuses much of that code base. Selections aren't made numerically. Think of trying to do a search in a date field, or for that matter just try it out. If you type in the internal number for that date, or some other representation of the date, the search won't return the matching date. It matches to the date character by character, not numerically.
Since set analysis is using this same code, the easiest way for QlikTech to handle dates in set analysis was to force us to make them match character for character instead of doing numeric comparisons. Set analysis is such a powerful feature that they surely wanted to get it out to us as quickly as possible. That meant reusing all the code that they could, cutting corners as necessary.
Proper date-to-date comparisons are most likely just one of those corners they cut in the effort to get set analysis to us as soon as possible. Perhaps they intended to add it later. Perhaps they didn't. At this point, they might be reluctant to change it, as many of us have adapted to its limitations, and they might be worried about breaking something that's working.
I'd still like to see them handle it better, though. As you say, this accounts for a large portion of the problems people have with set analysis. The whole text vs. number comparison thing is non-intuitive based on our normal experience with date comparisons in QlikView. This seems like a good opportunity to make the product more user-friendly by resolving an issue that lots of users encounter.
