topic Re: Count Cancel Date in QlikView

Count Cancel Date

Wed, 13 Jun 2012 20:23:52 GMT

Hi,

I'm tyring to create a column either through set analysis or on import (would like to have both solutions) where I add a counter indicating whether a user has cancelled. Here's a sample of my data:

On import, I tried the following, however, this returned a 1 for every record.:

If(isnull([Cancelation Date]),0,1) as HasCancel,

Using set analysis, I tried the following, but this doesn't work either.:

sum(if([Cancelation Date] = (">0"),1,0))

Again, the goal is to count or sum whether a user has canceled. If anyone has any insight on a solution on import and using set analysis that would be great.

Thanks in advance!

Matt

Re: Count Cancel Date

Wed, 13 Jun 2012 21:22:16 GMT

in the properties-presentation tab, is 'supress zero values' selected?

Re: Count Cancel Date

Wed, 13 Jun 2012 22:05:58 GMT

Hi,

Your first expression [ If(isnull([Cancelation Date]),0,1) ] looks right.

Are you sure that the "-" sign in your table represents NULL? Or, could it be a string?

If you can provide a sample qlikview application, it may be easier to answer.

Regards,

http://quickdevtips.blogspot.com/

Re: Count Cancel Date

Wed, 13 Jun 2012 22:07:39 GMT

have you tried your isnull as an expression in the graph?

Re: Count Cancel Date

Mon, 18 Jun 2012 17:32:52 GMT

Hi,

Turns out the "-" wasn't null so I was able to use the following the count the cancels:

sum({<[Cancelation Date] -= {"-"} >}[Transaction Counter])

Thanks both for the help, it's much appreciated.

Matt

Re: Count Cancel Date

chematos — Mon, 18 Jun 2012 18:39:06 GMT

Why don´t you create a field in the load script for each Cancelation Date ??

Load *,

if (not isnull([Cancelation Date],1, 0) as Cancelations

from tableX;

In that way, you will just need to make a sum(Cancelations) in the your expression but make sure that your field has null values, not '-' character