My Data model is based on Link Tables concpet. DateDim QVD is connected very well and I work with below 2 fields from it:- Date : 20-08-2025 DateKey = 20250820
However, I have an Expression in the Straight Table wherein, Sum ({<Date=>} PerformanceFee 😞 which works fine but give me summation for all the values in the QVD for all the dates whereas, I am looking for is Sum ({< Date=, FixingDate = {"=$(Max(FixingDate)"} >} PerformanceFee ) where, FixingDate is in YYYYMMDD format just like DateKey and is Q2's end date i.e. 20250630.
I cannot sort the QVD in the script as there are 5 more such QVDs in the model and all of them are set of different date than the main one.
My set expression is not working as I am selecting "Date" field on UI from the Filters and my expression concerns with FixingDate which is unselected and nowhere on the UI.
I believe it's a syntax error, caused by some confusion with advanced search, $-sign expansions, etc... - unless you typed the expression here with extra syntax errors...
Let's start peeling the onion...
1. $(Max(FixingDate) - that's a $-sign expansion, and it requires an equal sign before the Max() function. It's also missing a closing parenthesis. So, the correct way to calculate it would be this:
$(=Max(FixingDate))
2. Then, you enclosed it in an advanced search condition - enclosed in double quotes with an equals sign:
{"=..."}
Advanced search is different from other types of searches. It contains a condition that's going to be evaluated for each value of the field that it applies to - in this case, the Fixing Date. Naturally, for each Fixing Date it will return its own value as the Maximum. The advanced search condition renders true if it returns a number that is not zero. So, in this case any date will return a true result, so all dates will be included.
I believe in your case you don't want any search at all - just to select the max value. Then, you should formulate it like this:
Sum ({< Date=, FixingDate = {$(=Max(FixingDate))} >} PerformanceFee )
If your date is a fully formatted (dual) date field, you may need to enclose the $-sign expansion in single quotes:
Sum ({< Date=, FixingDate = {'$(=Max(FixingDate))'} >} PerformanceFee )
I hope these ideas can help you get "un-stuck".
Allow me to invite you to my Qlik Expert Class that I'll be teaching in Vienna, Austria on September 22-24. I will be teaching advanced data modeling, along with advanced scripting, performance optimization, and a full day on advanced aggregation and Set Analysis techniques. You will learn the most advanced Qlik methodologies that will help you solve tough problems like this one.
