Counting based on particular date.

[AJ1806]

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!

[Quy_Nguyen]

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.

[Or]

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.

[Quy_Nguyen]

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.

[AJ1806]

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

[AJ1806]

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 !