Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
bjchristie
New 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? 

 

11 Replies
Partner
Partner

Re: set expression with dollar expansion

Whats the output when you put the inner expression in a text object?
i.e. Maxstring({1}timestamp#(batch_dttm,'DDMMMYY:hh:mm'))

gut feeling is that it will be wrong.
bjchristie
New Contributor II

Re: set expression with dollar expansion

What is a text object and how do you create one?

I will research this idea but asking you will explain any future delayed response on my part. 🙂

I am hoping that your answer will be faster than my research.

Partner
Partner

Re: set expression with dollar expansion

kpi chart or text and image chart in qlik sense
bjchristie
New Contributor II

Re: set expression with dollar expansion

Oh. ok.

I already have a KPI for.... Maxstring({1}timestamp#(batch_dttm,'DDMMMYY:hh:mm')) and it gives me the correct answer. '15MAR19:20:00'

So there must be something fundamental that I am not understanding.

I even hard-coded '15MAR19:20:00' into the KPI and it gives me the correct answer, but it will not resolve the $ expansion.

Partner
Partner

Re: set expression with dollar expansion

whats the data type for batch_dttm in your data model?
bjchristie
New Contributor II

Re: set expression with dollar expansion

I export the batch_dttm field as a datetime format from SAS to a CSV file.  I do not know how Qlik interprets it. I don't see any details about the field properties when I edit the table in the data manager.

Partner
Partner

Re: set expression with dollar expansion

Highlighted
bjchristie
New Contributor II

Re: set expression with dollar expansion

$ascii $text

Partner
Partner

Re: set expression with dollar expansion

hm...
are you using this expression with a chart? with other dimensions?
if so add a TOTAL qualifier there too Maxstring(TOTAL {1}..