Totals in Straight Table in QlikView

[miguelbraga]

Hi community,

I've been working on a dashboard and found myself in a little bit tricky situation. I have a Straight table with these dimension fields:

[CodEmpregado] labeled as [Funcionário], [DataHora] labeled as [Entrada], [Saída] labeled as [Saída] and I have two expressions like this:

• sum(tempo) labeled as [Horas trabalho]
• ((tempo*1440)/60)*(ValorHora) labeled as [Custo]

This is a snapshot with my Straight Table

What I'm trying to do here is to achieve the Total Value of all [Funcionário]. I've tried to change the [Custo] expression to this:

if( isnull ( num( ((tempo*1440)/60)*(ValorHora) ) ), num( 0 ), num( ((tempo*1440)/60)*(ValorHora) ) ) but it gives me 0 in Totals.

How can I extract the exact value of Custo -> should be 3 139,94€.

Notes: these values were based on two selections:

• mes = 'Oct'
• dia = '22'
  

Can you experts please help me? hic‌, gwassenaar‌, sunindia‌ can you help me? Anyone?

Regards,

MB

[Anonymous]

why don't you let qlikview do the calculation?

if you need a formula for the total:

sum(total <Functionario> Custo) should give you the correct sum

[miguelbraga]

Where should I use your expression?

[Anonymous]

the formula you can use in a textobject for instance (or you may add the formula as Expression into

your table, but then you get the same value in each row)

in the straight table (let qlikview do the Job itself) I would use

Dimension Limits: Show Total

or

Expression: Select Custo and define "Expression total"

[rubenmarin · Accepted solution]

Hi, tempo and ValorHora can have many values in totals row, so you need an aggregate function to tell QV what to do with all those values, ie:

Sum(((tempo*1440)/60)*(ValorHora))

or:

Sum(Aggr(((tempo*1440)/60)*(ValorHora), [CodEmpregado], [DataHora], [Saída]))

[miguelbraga]

Thanks mate you've solved my problem The expression correct was:

Sum(Aggr(((tempo*1440)/60)*(ValorHora), [CodEmpregado], [DataHora], [Saída]))