Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
prads_uk_2025
Contributor III
Contributor III

Set Analysis issue with 2 Dates

Hello All,

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.

Please help me solving this.

1 Reply
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi there,

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.

Cheers,

Oleg Troyansky