Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
AJ1806
Contributor
Contributor

Counting based on particular date.

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'}Qliksense_example.PNG

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!

5 Replies
Quy_Nguyen
Specialist
Specialist

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
MVP
MVP

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
Specialist
Specialist

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
Contributor
Contributor
Author

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

AJ1806
Contributor
Contributor
Author

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 !