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: 
Not applicable

Set analysis Question

Hello everyone,

I have a table like this:

DateTimeStatusTap_Id
01/01/2012 14:00open1
01/01/2012 15:00close1
02/01/2012 14:00open1
02/01/2012 4:30close1
02/01/2012 4:45open1
03/01/2012 20:00close1
03/01/2012 20:00open1

I want to build a simple chart that will present for each day and tap the last status  - open or close (the biggest datetime for each day)

Thanks in advance.

1 Solution

Accepted Solutions
sebastiandperei
Specialist
Specialist

Dear Matan, I like the people that never gives up!!

In first place, with a table with "date" as dimention (if you don't have the "date" field, you could create with a calculated dimention like =Left(date(DateTime),10)), the expression that gives to you the last state is:

=FirstSortedValue(State, -DateTime)

But, for make this with SA, you need to tell to the expression witch values are available. These available values will be only the max for each day. The expression would be:

Only({<DateTime={$(vMaxTime)}>}Status)

Because of sintaxis of SA, you cant use search query inside another search query, and to make that you need to make a variable. In this case, it would be:

='"*'&Concat(left(Timestamp(Aggr(max(DateTime),date)),16),'","*')&'"'

This variable creates a search string, with the values of the max for each day. From inside to outside, Aggr(Max(DateTime), date) gives you the max values aggregated by date. Then, with TimeStamp, you transform it to a timestamp string. I used Left to pick out the seconds, because if you use the [Format] parameter of TimeStamp, you could give round problems, and have a difference of minutes, creating invalid timestamps. Note that if you have hours less than 10, you should use left(...., 15). I recomend that you format these field to HH:MM:SS, or HH:MM and don't use Left.

Then, the Concat creates the complete string with all the max values.

Im agree with you, IT IS POSSIBLE!!

View solution in original post

16 Replies
Not applicable
Author

Hi Matan,

Presuming you don't wish to edit the script (which would be the easiest and most efficient way to do this) this is how I would construct my chart:

Dimension: Add calculated dimension of

=date(floor(Datetime))

this will remove the time from the timestamp and group your items as just a date (Date() on it's own migiht work, but I haven't tested it as a dimension)

Expression "latest Status" : =firstsortedvalue(Status, -DateTime)

Firstsorted value takes the first status when you order by "-Datetime" IE the "last" in the day.

Regards,

Erica

Not applicable
Author

Thanks....i didnt think about using this function.....

just for the knowledge - can it been done by using set analysis ?

Not applicable
Author

yes, but it would be more complicated. Something like

=only( {$<Datetime= {$(=Datetime) }  >}   Status)

This will search for the "only" value of status where the datetime is the same as the max datetime.

Regards,

Erica

Not applicable
Author

Thanks again,

Somehow the set analysis expression isnt working for me.

I would appreciate if you could look in this qvw.

perumal_41
Partner - Specialist II
Partner - Specialist II

Hi,

I have attached one Application .This Application helpful to u.

Reegards

perumal

Not applicable
Author

I was also thinking...

Perumal A: your solution isn't setAnalysis, is it?

A problem in that solution (as well as in the solution with firstSortedValue) is the date 3-1-2012. In this QVW it doesn't appear, while it should. The cause is that the datetime is exactly the same (and the firstSortedValue has >1 record and will be null...) Perhaps this is more a data-cleaning thing?

In SetAnalysis I'm afraid it will be hard, So I wonder what the reaction of Erica Whally is. I think in the load-script then first the max-date must be calculated and available per column, so that then the setanalysis will work...

azimabadi
Creator III
Creator III

Hi Matan,

first change dimension in your QVW from "Tap_ID" to "date". This make things to look a few better. But problem is your Expression where you have used : DateTime={"=max(DateTime)"}. It's not the way you can use functions in an Expression defination. ofcurse you can define a variable like this :

='only({$<DateTime={"' & =max(DateTime) & '"}>}Status)'

and then write $(varName) as Expression,but max() will be called only one time when QV wants to refresh the chart, not for every row with a different value of max(DateTime).

I think the best way is what Erica has mentioned in first post.

Best Regards

Peyman

Not applicable
Author

So that would mean that this is not possible with SetAnalysis, right?

azimabadi
Creator III
Creator III

I think so, Generaly set analys is for filtering on an expression, for all values. I don't know any way that can be used in the way that you mean.