topic Re: Counting based on particular date. in App Development

Counting based on particular date.

AJ1806 — Mon, 12 Apr 2021 08:06:46 GMT

Hi All,

I am trying to write an expression with date condition.
When the date <= '3/15/2021' the count will be based on filter1 = {'1'} and filter2 = {'a'}

and if the date > '3/15/2021' the count will be based on filter1 = {'2'} and filter2 = {'a'}.

My output should be :

For 3/15/2021 the count is 2 and for 3/29/2021 the count is 3.

I tried with the following expression , but its failing with the "Error in Expression ')' expected"

=If(date = ("<=3/15/2021"),
count({<filter1 ={'1'},filter2 = {'a'}>} filter2),
count({<filter1 ={'2'},filter2 = {'a'}>}filter2))

I'd appreciate if I could get the best method/syntax to produce this result?

Thank you in advance!

Re: Counting based on particular date.

Quy_Nguyen — Mon, 12 Apr 2021 08:27:03 GMT

Hi,

try this:

If(Date(date_field) <= MakeDate(2021,03,15), count({<filter1 ={'1'},filter2 = {'a'}>} filter2), count({<filter1 ={'2'},filter2 = {'a'}>} filter2))

make sure your date field is in correct date format.

Re: Counting based on particular date.

Or — Mon, 12 Apr 2021 08:48:06 GMT

This still won't work correctly, I think - it'll work if the date field is a single value and you just need to pick a count formula, but it won't work if you have multiple rows with dates that may be older or newer than the value. In that scenario, the if() statement needs to be inside the count, or you need two count statements added together.

Two count version would look something like this - can't test the syntax:
count({<filter1 ={'1'},filter2 = {'a'}>} If(Date(date_field) <= MakeDate(2021,03,15),filter2))

+
count({<filter1 ={'2'},filter2 = {'a'}>} If(Date(date_field) > MakeDate(2021,03,15),filter2))

The other approach would be to put the date_field condition inside the set analysis, which is a little more tricky to write out without the actual dataset so I'm not going to attempt it in a text editor, but I'm sure someone can supply the specific syntax.

Re: Counting based on particular date.

Quy_Nguyen — Mon, 12 Apr 2021 09:27:11 GMT

You're right. I was just thinking about correct his expression to show it in a table with single date value. Your suggestion is better, it works for all cases.

Re: Counting based on particular date.

AJ1806 — Mon, 12 Apr 2021 09:40:44 GMT

Hello Quy_Nguyen,
This expression is working for me , thank you!

Re: Counting based on particular date.

AJ1806 — Mon, 12 Apr 2021 09:52:01 GMT

Hello Or,

Yes, I have many dates which are older and newer than 15th of March. But this date is the threshold for the analysis.

I shall try the sum of the two count expression also.
Thank you !