topic Syntax Error - Month End Count + Condition in QlikView

Syntax Error - Month End Count + Condition

shumailh — Sun, 09 Aug 2009 20:00:15 GMT

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

Syntax Error - Month End Count + Condition

Anonymous — Sun, 09 Aug 2009 20:58:37 GMT

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

Syntax Error - Month End Count + Condition

shumailh — Sun, 09 Aug 2009 23:04:58 GMT

Hi Michael,

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

Regards
Shumail

Syntax Error - Month End Count + Condition

Anonymous — Sun, 09 Aug 2009 23:33:39 GMT

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.

Syntax Error - Month End Count + Condition

shumailh — Wed, 12 Aug 2009 15:08:56 GMT

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


=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))

Syntax Error - Month End Count + Condition

Anonymous — Wed, 12 Aug 2009 22:27:18 GMT

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.

Syntax Error - Month End Count + Condition

shumailh — Sun, 16 Aug 2009 12:06:57 GMT

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