Count Cancel Date

[#1]

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

[#2]

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

[#3]

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/

[#4]

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

[#5]

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

[chematos]

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