Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Count Cancel Date

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:

Screen shot 2012-06-13 at 10.10.54 PM.png

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


5 Replies
Not applicable
Author

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

Not applicable
Author

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/

Not applicable
Author

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

Not applicable
Author

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
Specialist II
Specialist II

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