set expression with dollar expansion

[bjchristie]

I recently asked how to write a set expression that would select the last run time (regardless of selection) using a date variable (calendar_dt) and a time variable (batch_time). The solution was

=time(max({1<calendar_dt={"$(=maxstring({1}calendar_dt))"}>}batch_time))

There is a third datetime variable in the data, batch_dttm, that has the format (DDMMMYY:hh:mm) and the data also has a variable that gives the server name (hostext).

I want to display the max value of a variable (measure) for each server (dimension) regardless of selection FOR the last run time. I have been studying "Les set analysis_ENG.pdf" that I found in the forums, but the proper syntax is still eluding me.

I have the following:

Max({1<hostext={S1234}, batch_dttm={'$(=Maxstring({1}timestamp#(batch_dttm,'DDMMMYY:hh:mm')))'}>}var1)

It returns a dash (-) However, I get a result if I hard code a batch_dttm, by replacing the red text with a value, for example:

Max({1<hostext={S1234}, batch_dttm={'15MAR19:20:00'}>}var1)

Why is the dollar expansion not working? How does qlik resolve it? When do you need a preceding equal sign? 

[bjchristie]

The complete expression looks like:

Max({1<hostext={S1234}, batch_dttm={'$(=Maxstring(TOTAL{1}(batch_dttm)))'}>}var1)

still returns dash (-).

I use this field in other KPI. Why would this matter?

[bjchristie]

I apologize in advance for the length of the following.... but I hope it helps so that I can understand and fix the problem.

I understand what the set expression is giving, I just don't know how to correct the syntax to give what I am expecting.

Additional background information about the data:

I have a model that runs at regular intervals on 3 separate servers (i.e.  hostext) and are at different development levels (i.e. model version). I have analysis code that summarizes the data by batch_dttm ('DDMMMYY:hh:mm') OR the calendar_dt/ batch_time pair (both derived from batch_dttm). There is one summary row by hostext, batch_dttm in the exported CSV file that gets loaded into qlik sense.

Due to code errors, system outages, or other issues, not all batch_dttm are represented in the data for each server (hostext). When I setup a #1 Simple KPI (from custom objects) with server (hostext) as dimension and ask for the aggregate of the last run time (batch_dttm) I don't get a result for one server, because it has an older batch_dttm than the other 2 servers (currently having the same last run time). Qlik is picking the maximum batch_dttm  (corresponding to the 2 servers) and consequently not displaying any result for the third server since it doesn't have any data at that batch_dttm. I am expecting Qlik to display the data from the last run time for each server. (I have a separate KPI that tells me what is the last run time for each server)

For the #1 Simple KPI, the following displays the correct result for 2 servers that have same max batch_dttm, but not the third:

avg({<batch_dttm={'$(=Maxstring({1}timestamp#(batch_dttm,'DDMMMYY:hh:mm')))'}>}var1)

Note: since there is only one row of data per server/batch_dttm it doesn't matter if I use min, max, mode, avg for the aggregation. count gives 1 as a result, as expected.

The aggregation & set expression above is subject to the current selection and results in a dash (-) if other data than the full application data is selected. I must add a 1 to the set expression (see below) to always display the results of the last run time, but qlik still only displays the results from the 2 servers, not the third that has a back-dated last run time.

avg({1<batch_dttm={'$(=Maxstring({1}timestamp#(batch_dttm,'DDMMMYY:hh:mm')))'}>}var1)

How can I tell Qlik to FIRST pick the last run time for each server THEN display the aggregate at that time???

If I can solve this issue then I should be able to solve the original question in this thread, where I am hard-coding the server dimension.