topic group by in QlikView

group by

Wed, 25 Nov 2020 16:16:04 GMT

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 — Sun, 14 Apr 2019 14:07:20 GMT

Re: group by

kamielrajaram — Wed, 26 Apr 2017 09:09:30 GMT

How do you identify the wrong dates?

Is the default date only for the null dates?

Regards

Kamiel

Re: group by

Wed, 26 Apr 2017 11:58:18 GMT

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

Re: group by

vinieme12 — Wed, 26 Apr 2017 12:03:08 GMT

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"

Re: group by

Anonymous — Wed, 26 Apr 2017 12:32:04 GMT

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?