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: 
danimelo1
Creator
Creator

Problem with formula on different QlikView versions

Hi all,

I'm working on a formula and the problem is that I can see the results in a table, but there is a problem when someone opens the same documents and the formula doesn't work. The formula is as follows:

 

=If(
    Aggr(NODISTINCT Count(watson_conversation_id), watson_conversation_id)=1, 0,
    Interval( Aggr( NODISTINCT FirstSortedValue(DISTINCT time, - time) , watson_conversation_id) 
              - Aggr( NODISTINCT FirstSortedValue(DISTINCT time, time) , watson_conversation_id), 'ss')*24*60*60)

 

I'm working with qlikview 12 and the QlikView version where this calculated measure is Versión 11.20.13206.0 SR13 64-bit Edition (x64)

Any idea, maybe a function which is not compatible? 

Labels (1)
1 Solution

Accepted Solutions
danimelo1
Creator
Creator
Author

Thanks for your advice @sunny_talwar . Finally I came up with this formula and it worked!

 

= Interval(
           AGGR(NODISTINCT Max(Time(Mid(time, 12, 8), 'hh:mm:ss')) - Min(Time(Mid(time, 12, 8), 'hh:mm:ss')), watson_conversation_id),
           'ss'
          ) *24*3600

 

As you pointed, maybe the problem was having multiple Aggr functions. 

View solution in original post

4 Replies
sunny_talwar

I don't think anything major changed between versions, but it could be a bug in a particular version.... by either ways, I would say that you at least simplify you expression to this

=Aggr(NODISTINCT 
  If(Count(watson_conversation_id) = 1, 0, Interval(FirstSortedValue(DISTINCT time, - time) - FirstSortedValue(DISTINCT time, time), 'ss')*24*60*60)
, watson_conversation_id)

Having multiple Aggr() is not a good idea and should be avoided if possible 

danimelo1
Creator
Creator
Author

Thanks @sunny_talwar  for your reply and your advice about the formula. I'm going to try with some versions and check if it's about a bug or what.

Brett_Bleess
Former Employee
Former Employee

Daniel, just adding a couple of Design Blog posts that may be somewhat useful to you in addition to Sunny's comments:

https://community.qlik.com/t5/Qlik-Design-Blog/The-nodistinct-qualifier-and-the-Aggr-function/ba-p/1...

https://community.qlik.com/t5/Qlik-Design-Blog/Pitfalls-of-the-Aggr-function/ba-p/1463275

Consider marking Sunny's post using the Accept as Solution button if his expression change did work, otherwise, consider returning and posting what your final solution was and mark that if you do something different.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
danimelo1
Creator
Creator
Author

Thanks for your advice @sunny_talwar . Finally I came up with this formula and it worked!

 

= Interval(
           AGGR(NODISTINCT Max(Time(Mid(time, 12, 8), 'hh:mm:ss')) - Min(Time(Mid(time, 12, 8), 'hh:mm:ss')), watson_conversation_id),
           'ss'
          ) *24*3600

 

As you pointed, maybe the problem was having multiple Aggr functions.