Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
User45
Partner - Contributor II
Partner - Contributor II

Excel formula

Hello, I have been working with Qlick for a short time and I have a question about how to write two formulas that I have created in Excel in order to know if a debt is pending or collected and in case is pending, know how many days are left for its collection.

The formulas are these 2:

=IF(F2<=TODAY();"Charged";IF(OR(E2>TODAY();F2>TODAY());"Pending";"Charged"))


=IF(J2="Pending";DATEDIF(TODAY();F2;"D");"Charged")


I would like to use create these 2 formulas directly in Qlick through calculated fields , and thus not having to add those 2 new columns in Excel.

The Excel has this shape:

Captura.PNG

Thank you very much in advance.

 

 

 

8 Replies
QFabian
Specialist III
Specialist III

Hi @User45 , this is almost the same :

Using the name of columns from first row, an changing

" --> '

; --> ,

 

IF([collection date field] <= today(), 'Charged', if([expiration date field] > today() or [collection date field] > today(), 'Pending', 'Charged'))

IF(Concept = 'Pending', today() - [collection date field] ,'Charged')

QFabian
User45
Partner - Contributor II
Partner - Contributor II
Author

Thank you very much but there must be a part of the function that I am not writing well because it is marked in red at the end...

(The fields are in Spanish)

Captura.PNG

QFabian
Specialist III
Specialist III

Hola, revisé l expresió como medida maestra o como medida en un grafico, y no me aparece problema, cómo llegaste a la ventana que muestras?

QFabian
User45
Partner - Contributor II
Partner - Contributor II
Author

En español entonces 😁 Pues como lo quiero insertar como una columna creada en la propia tabla fui a Gestor de datos -> Lápiz para editar la tabla, y ahí se muestra la opción de crear campo. 

Captura2.PNG

 

Es raro porque como dices, como medida sí lo toma correctamente. Y lo del campo calculado, ya lo hice antes con otras medidas y no tuve problema...

Captura.PNG

 

QFabian
Specialist III
Specialist III

Hola!, al parecer el problema es el OR :

QFabian_2-1617207419231.png

prueba con otro if anidado ´.

if([Fecha Cobro]<=today(), 'Cobrado', if([Fecha Vencimiento]> today(), 'Pendiente', if([Fecha Cobro] > today(), 'Pendiente', 'Cobrado')))

QFabian_1-1617207282127.png

 

 

QFabian
User45
Partner - Contributor II
Partner - Contributor II
Author

Muchas gracias amigo! Me sirvió para crear el primer campo por fin. El segundo lo tengo casi completado, solo que únicamente me falta calcular los días restantes respecto a la fecha de vencimiento.

Es decir, cuando el estado es pendiente solo me calcula los días cuando existe una fecha de cobro, pero cuando solo existe la fecha vencimiento,  me devuelve un - .meme.PNG

 Necesitaría como un Y en Excel para que me cogiese ambas...

pepe.PNG

De todas formas muchísimas gracias por la ayuda:)  

EricTaylor55
Contributor
Contributor

I understand your concern about not wanting to add new columns in Excel and instead create the formulas directly in Qlik. Fortunately, you can easily do that through the "Edit Script" option in Qlik. Simply navigate to the script editor and use the "if" and "date" functions to replicate the formulas you created in Excel. Once you have written the formulas in the script, you can use them as calculated fields in your Qlik application. Also, I noticed that you are working with financial data. Have you heard of wallstreetoasis.com? It's a great website to learn more about financial modeling, including the DCF model.

Chanty4u
MVP
MVP

First formula

=if(F2<=today(), 'Charged', if(E2>today() or F2>today(), 'Pending', 'Charged'))

 

Second formula 

=if(J2='Pending', num(F2 - today(), '####'), 'Charged')