topic Re: can someone please check my Syntax in QlikView

can someone please check my Syntax

samuel_brierley — Thu, 30 Jul 2015 07:59:08 GMT

Hi all,

Im convinced this is correct but obviously not lol

=count({<Clearance_Time = {$(>num(MonthEnd(Raised_Timestamp)))}>}Clearance_Time)

to check this Ive separated the parts out into the below table to help me work out the problem....didn't help

if its not obvious what i'm trying to do is count the occurrences where an item is cleared in the month after it was raised.

Re: can someone please check my Syntax

marcus_malinow — Thu, 30 Jul 2015 08:06:24 GMT

Try this:

count({$<Clearance_Time={">$(=num(MonthEnd(Only(Raised_Timestamp))))"}>} Clearance_Time)

If you are not specifying a specific value for your Set Analysis, you need to use a search string (denoted by the set enclosed in double quotes, and the '>', so we are searching for values greater than the value within the set.

Also, you need some sort of aggregation for the set value - in this example i've used Only as I imaging their may be only one value of Raised_Timestamp for each Clearance_Time.

Re: can someone please check my Syntax

samuel_brierley — Thu, 30 Jul 2015 08:12:56 GMT

Thanks for trying guys but neither of the above result in a number and from the Image you can see that this shouldn't be the case.

I've attached my qvw if that helps, sheet 3 contains the tables im looking at here.

I'm only a personal user so if you get to a solution please post it in as text

thanks for all the help guys

Re: can someone please check my Syntax

Gysbert_Wassenaar — Thu, 30 Jul 2015 08:26:03 GMT

Create a new field in the script in a preceding load that checks if the clearance time is larger than the monthend time of the raised time.

[Fault Table]:

LOAD *, If(Clearance_Time> MonthEnd( Raised_Date),1,0) as ClearedLate;

LOAD Fault_Id,

Fault_State,

...etc

You can then use this new field in your count expression: count({<ClearedLate={1}>}Clearance_Time)

Re: can someone please check my Syntax

Thu, 30 Jul 2015 08:28:15 GMT

Just change the expression as below.

count({$<Clearance_Time={">$(num(MonthEnd(Only(Raised_Timestamp))))"}>} Clearance_Time)

Re: can someone please check my Syntax

samuel_brierley — Thu, 30 Jul 2015 08:30:50 GMT

Thanks,

I can now spend the time wrapping my head round why my way wouldn't work.

Re: can someone please check my Syntax

Gysbert_Wassenaar — Thu, 30 Jul 2015 08:38:00 GMT

That answer is not correct. It will return the same as count(Clearance_Time). You can see in the screenshot below how the dollar expansion is evaluated: