topic Problem with formula on different QlikView versions in QlikView

Problem with formula on different QlikView versions

danimelo1 — Sat, 16 Nov 2024 02:05:14 GMT

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?

Re: Problem with formula on different QlikView versions

sunny_talwar — Wed, 30 Oct 2019 12:21:00 GMT

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

Re: Problem with formula on different QlikView versions

danimelo1 — Wed, 30 Oct 2019 17:57:06 GMT

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.

Re: Problem with formula on different QlikView versions

Brett_Bleess — Thu, 07 Nov 2019 13:16:11 GMT

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/1535332

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

Re: Problem with formula on different QlikView versions

danimelo1 — Thu, 07 Nov 2019 15:34:56 GMT

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.