group by

[#1]

Hi:

I am trying to create a report of due bills. For that I have the full database of invoices and payments of each client.

If I sum the amounts per client, I can see the outstading amount for each client. That works without issues.

The problem comes when I try to segment that by period (0 - 30 days, 31-60 days, etc). I do that with set analysis and also works with no issues.

The problem is that some of the pyments have the wrong date (or no date at all), so the system put the date 01-01-1900 by default.

What I am trying to do is to group all transactions corresponding to an invoice keeping the due date of the invoice without taking into account the wrong date of payment (which is 01-01-1900). For example:

Client     Invoice     Description     Due Date          Amount

AA          14              Invoice          01-04-2017      100

AA          14              Payment        01-01-1900     -100

Hope I explained myself well....

Thanks and best regards,

[xyz1]

.

[kamielrajaram]

Hi

How do you identify the wrong dates?

Is the default date only for the null dates?

Regards

Kamiel

[#4]

Hi Kamiel:

Thanks for yor answer. The problem is that the accounting software is the one who set the date (01-01-1900) when blank, so Qlikview receives a valid date which is 01-01-1900

regards,

German

[vinieme12]

Can you post an excel sample and also explain a desired output ?

I don't understand what you mean by this

"What I am trying to do is to group all transactions corresponding to an invoice keeping the due date of the invoice without taking into account the wrong date of payment"

[Anonymous]

Hi,

I had this issue when i was doing Nprinting some of the Qlik report.

I have sorted this by changing the source date format in the properties.

Probably there may be an option to look in from the source system.

What is the date format it has got?