filtering between two dates in chart expression

[sanelisai]

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

[kaushiknsolanki]

Try changing single quote to double quote.

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

[MayilVahanan]

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)

[sanelisai]

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

at lost, sanelisai 🙂

[MayilVahanan · Accepted solution]

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)

[sanelisai]

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

[sanelisai]

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