Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bjchristie
Contributor II
Contributor II

Set analysis

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?

 

 

Labels (3)
1 Solution

Accepted Solutions
Vegar
MVP
MVP

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)

image.png

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().

View solution in original post

8 Replies
Anil_Babu_Samineni

Perhaps this?

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
bjchristie
Contributor II
Contributor II
Author

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

returns a dash (-).

Vegar
MVP
MVP

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)

image.png

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().

bjchristie
Contributor II
Contributor II
Author

(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.

bjchristie
Contributor II
Contributor II
Author

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)

Vegar
MVP
MVP

Didn't my posting above solve this issue? Your last run time is found in the total row of the table. If you put that expression in a text box or a chart without any dimensions you will only get that final value.

You could also get the values over all dimensions by using the TOTAL attribute.
bjchristie
Contributor II
Contributor II
Author

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!!

bjchristie
Contributor II
Contributor II
Author

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)?