topic Re: Count if in QlikView

Count if

samuel_brierley — Fri, 30 Aug 2013 08:06:03 GMT

I have the follwing expression in Qlikview, It works when the [Date Cleared] is text but not when it refers to a collumn.

what its supposed to do is count how many times the date detected is the same as date cleared.

COUNT( {$<[Date Detected]={[Date Cleared]}>} [Fault Status] )

I would appreciate if you could explain where im going wrong as im trying to learn qlikview from an excel background and the above expression is subtly different.

thanks for your help

Re: Count if

Fri, 30 Aug 2013 08:13:36 GMT

Hi,

Try this If([Date Detected]={[Date Cleared],Count([Fault Status] ),'Do something if both are not same ').

Hope it helps you .

Thanks and Regards,

Amuthabharathi.

Re: Count if

MayilVahanan — Fri, 30 Aug 2013 08:15:14 GMT

Try like this

COUNT( {$<[Date Detected]={"=[Date Detected] =[Date Cleared]"}>} [Fault Status] )

Re: Count if

michael_maeuser — Fri, 30 Aug 2013 08:15:58 GMT

if both dates are in the same table, i would do an if statement in the script:

load

...,

if([Date Detected] = [Date Cleared], 1, 0) as xxx

on the frontend you can sum(xxx)

thats the best way in my opinion.

in set analysis it must be the same format (exactly)! maybe that is the reason

Re: Count if

CELAMBARASAN — Fri, 30 Aug 2013 08:17:08 GMT

Try with this

COUNT( {$<[Date Detected]={"=[Date Detected]=[Date Cleared]"}>} [Fault Status] )

Re: Count if

Fri, 30 Aug 2013 08:17:49 GMT

In Set Analysis Qlikview always compares the text values of the fields, not the numeric value. So when comparing dates make sure you have the same formatting applied to that field, and not compare, say, "1 Jan 2013" with "01/01/2013"

Re: Count if

samuel_brierley — Fri, 30 Aug 2013 08:31:07 GMT

there nust be someting wrong with my formatting as it gives the correct figures for 2 days in the month but for the life of me i dont know. I have used the format printer in excel to make sure everyhting is exactly the same and nothing

Re: Count if

Fri, 30 Aug 2013 09:01:52 GMT

first stable the format of the dates, they might be in character format

use LEFT, RIGHT, MID to divide the whole string then by using MAKEDATE create the date then use it in the expression

hope it solve ur problem.......

Re: Count if

samuel_brierley — Fri, 30 Aug 2013 09:18:47 GMT

I continued to play with all the expressions given and this eventually worked for me.

( {$<[Fault Id]={"=[Date Detected]=[Date Cleared]"}>} [Fault Id] )

can someone confirm that I understand this correctly, its saying count [Fault Id] if [Date Detected] =[Date Cleared].

I havnt a clue what the last [Fault Id] is for or what the <> are for.

thanks for all the help guys

Re: Count if

Fri, 30 Aug 2013 09:21:45 GMT

Hey,

Both the fields need to be converted to date format and try using the below formula:

count({<[Date Detected]={"=$([Date Cleared])"}>}[Fault Status])

Regards,

Basil