Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
bjchristie
Contributor II
Contributor II

set expression with dollar expansion

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? 

 

Labels (3)
11 Replies
bjchristie
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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.