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: 
shumailh
Creator III
Creator III

Syntax Error - Month End Count + Condition

Hi,

I am trying to count the month end numbers of a field AIF where AIF in P and A but it's not working using below code, Can any one please help


count(if(LoadDate_Active=monthend(LoadDate_Active), {$<AIF = {"P"}> + $<AIF = {"A"}>} AIF))


Regards,
Shumail Hussain

1 Solution

Accepted Solutions
Anonymous
Not applicable

Shumail,
Try first the form without set analysis:
count(if(LoadDate_Active=monthend(LoadDate_Active) and (AIF='P' or AIF='A'), AIF))
If it works, and you still want to use set analysis, try to change it to:
count({$<AIF={"P","A"}>} if(LoadDate_Active=monthend(LoadDate_Active),AIF))

View solution in original post

6 Replies
Anonymous
Not applicable

Shumail,
Try first the form without set analysis:
count(if(LoadDate_Active=monthend(LoadDate_Active) and (AIF='P' or AIF='A'), AIF))
If it works, and you still want to use set analysis, try to change it to:
count({$<AIF={"P","A"}>} if(LoadDate_Active=monthend(LoadDate_Active),AIF))

shumailh
Creator III
Creator III
Author

Hi Michael,

I tried but both of the code are not working... Tongue Tied

Regards
Shumail

Anonymous
Not applicable

Well, the first expression is very straightforward. Can you tell what the "not working" means... If it doesn't return anything, it could be that there is no data matching the conditions, or maybe the date condition tries to compare data in different formats(?) In the latter case, you have to make sure that the format is the same, for example num(LoadDate_Active)=num(monthend(LoadDate_Active)) One more thing - make sure that the fields you use in a chart expressions are not the key fields.
shumailh
Creator III
Creator III
Author

No working means, it's showing no value as i know there's a value. As per the code it looks fine and ok. I tried it through typecasting but my system got hanged everytime.. Angry


=count(if(Date(LoadDate_Active, "DDMMYY")=Monthend(Date(LoadDate_Active, "DDMMYY")) and (AIF='P' or AIF='A'), AIF))


below is the message which appears:

Now I have tried the below code which is working fine and my system not going hanged.


=count(if(day(LoadDate_Active)=day(Monthend(LoadDate_Active)) and (AIF='P' or AIF='A'), AIF))


Anonymous
Not applicable

I see - when you define format, it must be in single quotes: date(LoadDate_Active, 'DDMMYY').
Be careful with day() function, it is unique only within one month.

shumailh
Creator III
Creator III
Author

Yes Michael, but it's surves my purpose as the project file is a daily one only and will be new for each months so i am not worried about it.

Regards
Shumail Hussain