topic Re: duplicated measures in QlikView

duplicated measures

master_student — Tue, 16 May 2017 16:49:28 GMT

Hello Guys,

I have a problem in my expressions, it displays the same numbers for the two columns. see bellow :

here are my expressions :

Durée

= if( ValueList( 'Durée' , 'Moyenne')='Durée' ,

if(CONGE='Y',12012,if(isnull(PRESENCE),0,PRESENCE/1440)),

DATE(avg( DISTINCT if(FERIE='N' and CONGE='N',if(PRESENCE>0,PRESENCE/1440)

)),'HH:mm') )

Pause :

= if( ValueList( 'Pause' , 'Moy')='Pause' ,

sum(pausetime),

DATE(avg( DISTINCT if(FERIE='N' and CONGE='N',if(PRESENCE>0,PRESENCE/1440)

)),'HH:mm') )

Re: duplicated measures

Anonymous — Tue, 16 May 2017 16:51:15 GMT

I suppose, you always end in this if path:

DATE(avg( DISTINCT if(FERIE='N' and CONGE='N',if(PRESENCE>0,PRESENCE/1440)

Re: duplicated measures

Peter_Cammaert — Tue, 16 May 2017 16:52:59 GMT

How is your Valuelist dimension defined? And why are you using two different ValueList calls in the two expressions? They should be identical to the Calculated dimension ValueList...

Re: duplicated measures

master_student — Wed, 17 May 2017 08:33:17 GMT

Hello Guys,

Thanks for your interactions

I succeeded in displaying my two measures for the 10 last days :

here 's my dimension value list :

=ValueList('Durée' ,'Pause')

My expression :

= if( ValueList( 'Durée' ,'Pause')='Durée' ,

if(CONGE='Y',12012,if(isnull(PRESENCE),0,PRESENCE/1440)),

If( ValueList( 'Durée' ,'Pause')='Pause' ,

Hour(Date(sum(pausetime)/1440,'hh:mm:ss'))&'H:' &Minute(Date(sum(pausetime)/1440,'hh:mm:ss'))&'Min',

))

but I need to calculate the average of the two measures somme of (duree) the last 10 days / 10

How to do it pleasee?

Thanks in advance

Re: duplicated measures

Anonymous — Wed, 17 May 2017 18:49:19 GMT

try for this part

...

Hour(Date(

rangeavg(before(total sum(pausetime),0,10)

/1440,'hh:mm:ss'))&'H:' &Minute(Date(

rangeavg(before(total sum(pausetime),0,10)

/1440,'hh:mm:ss'))&'Min'

...

btw I think a part of your expression is missing in your last answer.

alternatively you could try

rangesum(before(total sum(pausetime),0,10)/10

instead if there exist null() values.