Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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?
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.