topic Re: filtering between two dates in chart expression in QlikView

filtering between two dates in chart expression

sanelisai — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have looked for and found a solution for my question from Cliq Community, but it doesn't work for me/I don't do it correctly:

sum ({< [payments.paydate] = {' >= 1.4.2020 <= 30.9.2020'}>} payments.amount)

I need to calculate a sum between two dates (above is the date format I'm using). How to make this correctly.

bs, sanelisai

Re: filtering between two dates in chart expression

kaushiknsolanki — Mon, 26 Oct 2020 10:24:49 GMT

Try changing single quote to double quote.

sum ({< [payments.paydate] = {" >= 1.4.2020 <= 30.9.2020"}>} payments.amount)

Re: filtering between two dates in chart expression

MayilVahanan — Mon, 26 Oct 2020 10:25:17 GMT

Hi @sanelisai

Can you try with double quotes instead of single quotes and also, confirm the date format.

sum ({< [payments.paydate] = {" >= 1.4.2020 <= 30.9.2020"}>} payments.amount)

Re: filtering between two dates in chart expression

sanelisai — Mon, 26 Oct 2020 10:41:41 GMT

Thank you for the answer. The double quotes don't seem to help. But how should I confirm the date format?

at lost, sanelisai 🙂

Re: filtering between two dates in chart expression

MayilVahanan — Mon, 26 Oct 2020 10:44:42 GMT

Hi @sanelisai

Create a listbox with payments.paydate field and confirm the date format is "dd.mm.yyyy" or "mm.dd.yyyy" or dd/mm/yyyy or any other format..

If its different format, then change the values in set analysis like

sum ({< [payments.paydate] = {" >= 1/4/2020 <= 30/9/2020"}>} payments.amount)

Re: filtering between two dates in chart expression

sanelisai — Mon, 26 Oct 2020 12:23:08 GMT

Hi @MayilVahanan

thank you again for your answer! I confirmed the date and it's ok. The function doesn't work anyway. Could the problem be in autocalendar? I have now tried both (payments.paydate.autoCalendar.Date] and [payments.paydate] and for both of them get zero.

br, sanelisai

Re: filtering between two dates in chart expression

sanelisai — Mon, 26 Oct 2020 12:49:51 GMT

Thanks again, @MayilVahanan, now it works! My last mistake was to have spaces in the filter. Should have been (like you had stated): {">=1.4.2020 <=30.9.2020"} . My version was {">= 1.4.2020 <= 30.9.2020"}.

br, sanelisai