Discussion Board for collaboration related to QlikView App Development.
I need to get the expired balance by range. (ie. 0-30 days, 31-60 days, 61-90 days...) for the customers account.
I'm getting the total expired balance easily, but I don't k now how can I "split" this information into the ranges of how many days is the charge expired.
In the attached files, i've uploaded an excel that contains a simplified example that is almost working in QV, and real data of a customer (of course, i've anonymized it)
Also, i've uploaded a qvw file with the formulas i'm using, but this formulares are almost working for the simplified example, not the real one.
I don't know if there is a simple way to calculate this.
Hope somebody can help me.
DataTableTmp: LOAD Customer, Number, [Expiration Date] as FechaVencimiento, Interval(Today() - Date([Expiration Date]), 'D') as ExpirationDays, Charge as Cargo, Payment as Abono FROM [Example Balance.xlsx] (ooxml, embedded labels, table is [Real Data]); DataTable: LOAD Customer, Number, FechaVencimiento, ExpirationDays, If(ExpirationDays > '120', 'Expirated 120+', If(ExpirationDays <= '120' and ExpirationDays > '90', 'Expirated 91 - 120', If(ExpirationDays <= '90' and ExpirationDays > '60', 'Expirated 61 - 90', If(ExpirationDays <= '60' and ExpirationDays > '30', 'Expirated 31 - 60', 'Expirated 0 - 30')))) as Range, Cargo, Abono Resident DataTableTmp; DROP Table DataTableTmp;