Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am new to Qlik Sense and I am creating an app to monitor the output of a model. The output is generated in SAS and exported as a csv file and imported to Qlik Sense. The model runs every 2 hours on business days starting at 10am - 8pm. The csv file has 3 date/time columns. I want to display the Last Run Date and Last Run Time of the application data. The 3 date/time columns are: calendar_dt (YY-MM-DD), batch_dttm (DDMMMYY:hh:mm), and batch_time (hh:mm).
I have successfully figured out how to select the Last Run Date: max({1}calendar_dt).
My attempts to select the Last Run Time always results in 20:00 which is the maximum of all the application data, NOT the Last Run Time on the Last Run Date.
The following syntax gives the maximum batch_time of ALL application data:
max({<calendar_dt={"=max({1}calendar_dt)"}>}batch_time)
I verified this by substituting a different numeric variable in for batch_time and the result was indeed the maximum of all application data.
What do I need to add to the above syntax to give the maximum batch_time of the maximum calendar_dt, regardless of the data selection?
Using this sample data set
LOAD * inline [ calendar_dt,batch_time,Department 2019-04-03,9:56,Human Resources 2019-04-02,9:15,Human Resources 2019-04-01,5:06,Legal 2019-04-01,12:53,Training 2019-04-01,15:02,Training 2019-04-02,23:17,Accounting 2019-04-03,19:02,Product Management 2019-04-03,9:56,Marketing 2019-04-03,9:44,Engineering 2019-04-02,21:41,Legal ];
I was able to get this table (its QlikView, but you will get the same result in Qlik Sense)
By using this formula:
=time(max({1<calendar_dt={"$(=maxstring({1}calendar_dt))"}>}batch_time))
A set modifier will always compare the string value of a dual() value. The date format is an dual().
Perhaps this?
max({1<calendar_dt={"$(=max({1}calendar_dt))"}>}batch_time)
max({1<calendar_dt={"$(=max({1}calendar_dt))"}>}batch_time)
returns a dash (-).
Using this sample data set
LOAD * inline [ calendar_dt,batch_time,Department 2019-04-03,9:56,Human Resources 2019-04-02,9:15,Human Resources 2019-04-01,5:06,Legal 2019-04-01,12:53,Training 2019-04-01,15:02,Training 2019-04-02,23:17,Accounting 2019-04-03,19:02,Product Management 2019-04-03,9:56,Marketing 2019-04-03,9:44,Engineering 2019-04-02,21:41,Legal ];
I was able to get this table (its QlikView, but you will get the same result in Qlik Sense)
By using this formula:
=time(max({1<calendar_dt={"$(=maxstring({1}calendar_dt))"}>}batch_time))
A set modifier will always compare the string value of a dual() value. The date format is an dual().
(A): max({<calendar_dt={"=max({1}calendar_dt)"}>}batch_time) and
(B): max({1<calendar_dt={"=max({1}calendar_dt)"}>}[batch_time])
both (A) and (B) give the same incorrect answer (20:00), I am expecting 16:00.
It is my understanding the expression between " " is a search 'string', in this case a max function, and will search for the maximum value of calendar_dt in the entire application data because of the {1} in the inner max function. So the outer max function should find the maximum batch_time in this subset, but it is still looking at the entire application data and returning 20:00.
It seems that everything between < > does absolutely nothing.
Since the model runs regularly (10:00, 12:00, 14:00, 16:00, 18:00 and 20:00) it is more difficult to debug with batch_time so I switched to a different variable instead but with the same syntax.
(B) gives the same maximum result no matter which calendar_dt is selected. (The maximum value of all application data). {Due to the 1 before < > ??}
(A) gives the maximum value of the user selection. not corresponding to the maximum calendar_dt.
I need to figure out how to actually restrict the max(batch_time) function to work on a subset of the data where calendar_dt is maximum (Last Run Date).
I know that max({1}calendar_dt) gives me the Last Run Date, as expected, regardless of the user selection.
I was finally able to get this expression to work after inserting an (=) between the first double quote (") and the ($), but it still gives the batch_time corresponding to the max of all application data, not the subset of the max calendar_dt.
max({1<calendar_dt={"=$(=max({1}calendar_dt))"}>}batch_time)
Your original post did resolve my issue, however, I was writing a response to the previous reply when you posted. I didn't think that maxstring would work since calendar_dt is not a string but a date. So, I will need to spend some time learning about dual(). Thanks!!
Now that I can determine the Last Run Date as well as the Last Run Time, how can I create a KPI that will aggregate a variable for that Last Run Date / Time?
Last Run Time =time(max({1<calendar_dt={"$(=maxstring({1}calendar_dt))"}>}batch_time))
In other words, is it possible to insert that into .... sum({1<Last Run Time>}var1)?