When bringing each part of the IF statement into different columns within a Table object, you can see the granular values and the aggregate value show correctly. However, when using the IF statement here, it results in 0. When the granular value matches the aggregated value, it should result in a 1. Attempting other methods like firstsortedvalue result in an invalid dimension error, even though it evaluates correctly, and other set analysis formulas attempted are not working. Using aggr(max([Episode Number]),PATID) by itself produces an additional record that should not appear.
I tried both formulas and received an 'invalid dimension' error when applying the expression. It evaluates in the expression editor, but gives the error in the table object.
I would think the aggr() is needed though, to specify how to aggregate if the lowest granularity (Episode Number) is being displayed in the table. Without it and just using Max(), the episode number value would be the same as the max value per record.
Just using Max(Episode Number]) gives an invalid dimension as well.
The weird thing is, the IF statement works with some of the records, giving a 1, but others where you can see the values tie in the individual columns, the IF statement results in 0. Somebody created this table object with formulas in other columns using firstsortedvalue and aggr, such as when displaying the PATID field. Could that be part of the issue, since I am trying to use PATID in my formula for the aggregate context? I don't understand though why the individual components show correctly, but the IF statement loses the context of the table. Even trying to use a [Name] field, versus [PATID], that does not have an expression on it, also throws the invalid dimension error.
Ok, if you want to create a calculated dimension you mustn't have an (outer) aggregation. I think you may need to include the check within the aggr() maybe with something like this:
That's a normal aggregation - that there is an aggr() inside the expression is meaningless. I think you need to review your datamodel to check if it's suitable for this view and if not to adjust it appropriate. Especially if the application is already rather slow it would be sensible to transfer the heaviest (pre-)calculations (if-loops, aggr() and interrecord-functions) into the script.
One of the very useful functions amongst the long list of functions that we saw in the previous tutorial is QlikView Agg() Function category. In this QlikView Aggregate Function, acesetm we are going to gain a better understanding of aggregate functions and also learn to apply these functions to our data.
