Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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
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.
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/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
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.