Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
rafael5958
Creator
Creator

calculate subtotal skiping strings

We have a dynamic table with this expresion:

 

if(previsao_entrega='-',null(),
	if(Round(previsao_entrega-Date(now()), 1)>0,Round(previsao_entrega-Date(now()), 1),'revisao'))

 

with subtotal itt shows:

rafael5958_0-1692380858713.png

How can I show sum as 17 and 33, and not the string revisao ? Calculate only numbers.

 

thank you

Labels (2)
1 Solution

Accepted Solutions
pedrohenriqueperna
Creator III
Creator III

Should "revisao" be considered a 0 or Null() when adding up?

If so, you could use Dual(). This function takes 2 parameters, the first is a text string that you want to display, and the second is a numeric value or expression that results in a numeric value (which can be sum and sorted as a number)

Try this:

If(previsao_entrega = '-', Null(), If(Round(previsao_entrega - Date(Now()), 1) > 0, Round(previsao_entrega - Date(Now()), 1), Dual('revisao', 0)))

////

 

O campo "revisao" deveria ser lido como 0 ou nulo?

 

Se sim, você poderia tentar usar a função Dual() que recebe um valor de texto para ser exibido e um valor númerico para ser interpretado.

 

Testa isso:

If(previsao_entrega = '-', Null(), If(Round(previsao_entrega - Date(Now()), 1) > 0, Round(previsao_entrega - Date(Now()), 1), Dual('revisao', 0)))

 

 

View solution in original post

1 Reply
pedrohenriqueperna
Creator III
Creator III

Should "revisao" be considered a 0 or Null() when adding up?

If so, you could use Dual(). This function takes 2 parameters, the first is a text string that you want to display, and the second is a numeric value or expression that results in a numeric value (which can be sum and sorted as a number)

Try this:

If(previsao_entrega = '-', Null(), If(Round(previsao_entrega - Date(Now()), 1) > 0, Round(previsao_entrega - Date(Now()), 1), Dual('revisao', 0)))

////

 

O campo "revisao" deveria ser lido como 0 ou nulo?

 

Se sim, você poderia tentar usar a função Dual() que recebe um valor de texto para ser exibido e um valor númerico para ser interpretado.

 

Testa isso:

If(previsao_entrega = '-', Null(), If(Round(previsao_entrega - Date(Now()), 1) > 0, Round(previsao_entrega - Date(Now()), 1), Dual('revisao', 0)))