Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

danimelo1
Contributor

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
Contributor

Re: Problem with formula on different QlikView versions

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

Re: Problem with formula on different QlikView versions

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
Contributor

Re: Problem with formula on different QlikView versions

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.

Digital Support
Digital Support

Re: Problem with formula on different QlikView versions

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 don't forget to use the "Accept as Solution" button on any posts that helped you resolve your problem or question.
danimelo1
Contributor

Re: Problem with formula on different QlikView versions

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