Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
This is a very simple question, but somehow I can´t find the answer...
I´m using Qlik Sense to visualize some data from a EXCEL file. There are several fields on the EXCEL file that show the amount of money owned for a particular length of time:
LOAD
FechaCorte,
lugar as OrigenFactura,
tipo,
rsocial1 as Vendedor,
code as NIT,
rsocial2 as Deudor,
fecha,
factura,
dia30 as deuda30Dias,
dia60 as deuda60Dias,
dia90 as deuda90Dias,
dia120 as deuda120Dias,
dia150 as deuda150Dias,
diamas as deudaMasde150Dias,
dia30 + dia60 + dia90 + dia120 + dia150 + diamas as DeudaTotal
FROM [lib://Soportes Recaudos (vliberal_rdimarco)/CarteraTotal.XLS]
(biff, embedded labels, table is [Cartera Total VL+PG$]);
So, I want to create a new field that shows the sum of all of these fields. I called that field "DeudaTotal" (TotalOwned). I did it like this:
dia30 + dia60 + dia90 + dia120 + dia150 + diamas as DeudaTotal
But somehow that new field is always Blank.
The other values load OK. Dia30 is the value of an invoice due for less than 30 days. Dia60 is between 30 and 60 days, and so forth.
Any help with the syntaxis of that sum is welcomed.
Thanks a lot!.
Try
RangeSum(dia30, dia60,dia90,dia120,dia150,diamas) as DeudaTotal
instead
Logically,your syntax is good; can u provide a sample excel file of this please; with aybe 10 rows?
Try
RangeSum(dia30, dia60,dia90,dia120,dia150,diamas) as DeudaTotal
instead
Yes, enclosed.
This might work for you.
LOAD
FechaCorte,
lugar as OrigenFactura,
tipo,
rsocial1 as Vendedor,
code as NIT,
rsocial2 as Deudor,
fecha,
factura,
dia30 as deuda30Dias,
dia60 as deuda60Dias,
dia90 as deuda90Dias,
dia120 as deuda120Dias,
dia150 as deuda150Dias,
diamas as deudaMasde150Dias,
sum( dia30 )+ sum(dia60) +sum( dia90) + sum(dia120) + sum(dia150) + sum(diamas) as DeudaTotal
FROM [lib://Soportes Recaudos (vliberal_rdimarco)/CarteraTotal.XLS]
(biff, embedded labels, table is [Cartera Total VL+PG$])
Group by OrigenFactura, tipo, Vendedor, NIT, Deudor, fecha, facture;
logically with adding the fields, we can have the sum of them :
LOAD
col1,
m1,
m2,
m3,
( m1+m2+m3) as somme,
col2
FROM [lib://Mariage/description.xlsx]
(ooxml, embedded labels, table is Sheet2);
result:
why isn't it always the case?
That did the trick!!
Thanks a lot!...
Probably some field is null.
May be it has to do with the fact that data is being loaded from Excel? I do not know...
Thanks for your help...
There are some null values on those fields...