Different aggregation criteria and sumif questions
Hello everybody,
i am kinda new with the product and I like it a lot.
I am working on a project for a telco company and I need to solve 2 problems:
- in a table I have to compare the number of the Customer of the selected date with the number of the Customers in a different and fixed date.
For example I select the data from 1 to 15 april and I want to compare the selection with my data at 31/03. The 1-15 april column is not a problem, is a simple aggregation and i have done it quickly. I am stuck at the other one.
I tried to use the following formula: =SUM(IF([Data riferimento]='31/03/2010', #USIM)) but is not working..
- The biggest problem i have is about multiple aggregation criteria. Most easy example is number of Customers. I have a simple logic hierarchy (zone-region). I have the data by zone and is easy to aggregate them into region by using sum().
The problem arise with time. If I select multiple days I would like the data to be aggregated by average and not by sum. I solved this by now aggregating with sum() and dividing the result for the number of selected days.. Is this correct or there is a more elegant way?
Problem is now that QlikView seems to have an internal recognition of dates and does not use the resulting string but converts it again to a date value - and that will not match.
I have tried all kinds of formulas and was only able to solve it with a variable being set to the string value "31/03/2010". But that is kind of an ugly solution, maybe we get a better answer from the forum.
I don't know if this will nail down your specific issues, but two thoughts crossed my mind when reading about your problem:
1) You are comparing a date with a string. I have noticed that unlike databases like Oracle and SQL Server which will automatically convert between strings and dates, QlikView can be very particular. I find it safer to use the Date() function to convert the data in my source table and then compare it to a date created using the MakeDate() function. Using these functions consistently when creating QVDs and scripts will help you struggle with failures to match similar data and may solve many formatting difficulties. Use this as a first step to make sure you are matching your dates correctly before moving on to work on the problem with eliminating hard-coded dates.
2) You may want to create an "island" table with only dates that doesn't share a column name with any other table in your QVW. You can select a date from this table and then incorporate that into a formula or bookmark. Substituting the selected value of this column for the value you had hard-coded should allow you select dates on-the-fly rather than hard-coding them into your formulas.
You have to specify [mese]=,[giorno]= to tell it to ignore the values in those fields. Otherwise, simply telling it the right value for Data riferimento will result in no data being found (because it's still ALSO trying to match mese and giorno). If your real application has additional fields in the calendar that are selectable, you'll have to specify all of them like that so that they'll be ignored.
In a set analysis expression, single quotes indicate a literal, double quotes indicate a search string. If you have one value, double quotes will still work (it will search for and find that one value), but I suggest using single quotes to indicate that it's a literal. I think it's a good habit to be in.
The $(=...) is a "dollar sign expansion". These are always evaluated first, and the result is inserted into the expression as if you had typed that exact text. So if this expression returns 31/3/2010, the first pass for QlikView is to literally convert it to this:
So we're using dollar sign expansion to get our "string literal" that we were looking for.
In this case, I doubt that the synthetic key being created is a problem. In fact, it should be internally almost identical to what would happen if you concatenated the two values yourself. Synthetic keys OFTEN indicate a problem, and you USUALLY want to only link on a single key. But in this case, at least on the surface, it looks fine to me to just leave the synthetic key alone.
In this case, we didn't need a date() expression in the set analysis because Data riferimento was stored in the default date format for this document. Otherwise we'd have needed to specify the format explicitly, like indicated in earlier posts. In set analysis, yes, you'll have to compare "like to like" in regards to dates. It's doing the same thing as would happen if you started typing the expression in a list box. If you type the numeric equivalent of a date in a date list box, it won't find the date. Maybe it should, but it doesn't, and set analysis is similarly limited.
"Island" tables with disconnected dates are very useful. They also have a lot of drawbacks. They're a quick and dirty solution to many problem, but there's usually a better solution.
Struggled a lot with this date format, surrounding the $(= ) with single quotes looks to be the key. (and is the example in the manual a typo with the added # after $( ?)
Page 359: sum( {$<Year = {$(#=Only(Year)-1)}>} Sales ) returns the sales for the previous year in relation to the current selection. Here, a dollar-sign expansion is used to calculate previous year.
I do not agree with your addition of [mese]= and [giorno]= as with sum ( {1< and only [Data riferimento] as condition I get correct results.
And as Lorenzo wanted to have the start date be the reference for the previous month we need to use the min function on Data riferimento and not max (sorry, don't want to be picky)
To Wayne: Had spent some hours on dates as strings instead of dates. For this example it looks to be no issue as I have also tried to convert the [Data riferimento] explicitly to a date in the load command (and it did not make any difference).
What still puzzel's me: Why is [Data riferimento] in the Set expression only allowing a string for comparison and not a date value - or am I wrong again.
Juerg Maier JmiD GmbH Schweiz wrote: is the example in the manual a typo with the added # after $( ?) sum( {tiny_mce_markerlt;Year = {$(#=Only(Year)-1)}>} Sales )
No, it isn't a typo, but was probably unnecessary for the example given, and isn't what you want for the date field in this thread. As best I understand it, the # here is a way of forcing the result to be an unformatted numeric. But Year here must already be numeric for them to be subtracting 1 from it, so the # should do nothing (though perhaps it will make certain that we get 2010 instead of 2.010 or 2,010). In our example, using # simply turns our nice date string back into its numeric equivalent, and thus breaks the expression.
Juerg Maier JmiD GmbH Schweiz wrote:I do not agree with your addition of [mese]= and [giorno]= as with sum ( {1< and only [Data riferimento] as condition I get correct results.
Use your expression, and then select Call Center = Cagliari. The dimension changes to Manager. But while the "Actual" column only shows results for Managers of Call Center Cagliari, the "Previous" column shows results for ALL managers. The {1 is our culprit. It tells QlikView to ignore ALL selections, such as our selection in Call Center. We probably don't want to ignore ALL selections. We probably just want to ignore DATE selections. To do that, I believe we must ignore each date field individually. It's a pain if you have a lot of calendar fields, but it's the only way I know to do it.
Juerg Maier JmiD GmbH Schweiz wrote:And as Lorenzo wanted to have the start date be the reference for the previous month we need to use the min function on Data riferimento and not max (sorry, don't want to be picky)
You're probably right. What I read was "Previous for me is the number of the Customer of the last day of the previous month of the selected date." It sounds like Lorenzo expects to only select ONE date, in which case min() and max() would return the same results. The question is what to do if multiple dates are selected. I'm betting that you're right, and that he'd prefer the min() date in that case. I'm just used to using max() as a way of getting the current date when no date is selected, so I just did that rather than thinking more deeply. My bad.
Juerg Maier JmiD GmbH Schweiz wrote:Why is [Data riferimento] in the Set expression only allowing a string for comparison and not a date value
My honest answer is that QlikTech probably took the easiest way out - the functionality already existed to select a date field based on a string search expression. No functionality existed to select a date field based on a numeric comparison. Rather than add the functionality so that set analysis could work in a more flexible manner, they took the easy way out and simply used the existing search capabilities. I can understand that. Set analysis is a great feature, and I think it was better for them to get it to market quickly than for it to be perfect on the first pass. But I do hope they expand on it later to allow for more direct date comparisons rather than string comparisons of dates. Having to format the date properly is a syntactic annoyance that shouldn't be required if the product were a little smarter.
