Using IF condition where the criteria is NE

[lucasdavis500]

I have a fairly large condition I'm trying to create for a text object, but I cannot get my Set Analysis or IF(NOT) functions to work properly.

Basically, what I have done is created a variable called Response Time in my data. I had a column with strings/numbers, and needed all of the values to be numeric, for calculation purposes, so I created a code for the strings, I.E. '1000' and '1001. None of my values will ever reach these numbers, so it's safe to keep them as codes.

I'm trying to do create some text objects based on calculations done on ALL of the values EXCEPT for the codes I created.

I.E., at a simple level:

=SUM({<[Response Time] -= {'1000','1001'}>} [Response Time]) / COUNT({<[Response Time] -= {'1000','1001'}>} [Response Time])

I want '1000' and '1001' excluded. I've also tried:

=(if(Not wildmatch([Response Time], '1000', '1001'), SUM([Response Time])) / (COUNT(Pkey)))

which returns '-'

The first formula is resolving, but it seems they it is keeping the '1000' and '1001' codes in the calculations, because the numbers generated are >100, which I know is not true, and the second returns NULL... am I missing something in the usage of if(Not wildmatch)?

[sunny_talwar]

It seems like it has something to do with how you are calculating your Response Time and assigning them the value of 1000 and 1001. Can you share your script where you manipulate Response Time?

[lucasdavis500]

//In my LOAD Script:

CET:

ALT(DateResolved, 'Null') as ResolutionDate,

LEFT JOIN(CET)

LOAD MAX(DateCreated) AS MaxDateCreated

RESIDENT CET;

//After I'm loading:

SecondCET:
LOAD *,
IF(ResolutionDate='Null',IF(MaxDateCreated-DateCreated>30, '1000', '1001'),ResolutionDate-DateCreated) AS ResponseTime

RESIDENT CET;
DROP TABLE CET;

ThirdCET:
LOAD *,
NUM(ResponseTime,'#,##0.00') AS [Response Time]

RESIDENT SecondCET;
DROP TABLE SecondCET;

The weird thing is, is that I actually manipulate [Response Time] in a different section of my script, like so:

IF([Response Time]>=0 AND [Response Time] < 7 , '0-7',
IF([Response Time]>7 AND [Response Time] <= 14 , '8-14' ,
IF([Response Time] > 14 AND [Response Time] <=21, '15-21',
IF([Response Time] > 21 AND [Response Time] <=30, '21-30',
IF([Response Time] = 1000, 'Outstanding >30',
IF([Response Time]=1001, 'Outstanding <30',
IF([Response Time]>30, '>30'))))))) AS ResponseBucket

When I use [Response Time] here, it seems to be working the way I want it to...

[hopkinsc]

hi, this seems to work for me, see attached. i have excluded Periods 1 and 2 in my example which works.

Its probably something to do with the field you are manipulating

[sunny_talwar]

Can you check by adding floor here?

ThirdCET:
LOAD *,
NUM(Floor(ResponseTime),'#,##0.00') as [Response Time]

[lucasdavis500]

Hi Sunny, this seemed to change all of my [Response Time] values to '-'

[sunny_talwar]

How about this:

ThirdCET:

LOAD *,

          Num(Floor(Num#(ResponseTime)),'#,##0.00') as [Response Time]

[lucasdavis500]

This has returned some of my values. The values are formatted as 1.00, 2.00, 3.00, etc... with 2 decimal places, but the decimals are not populated.... Which they should be in some cases... My IF/SET analysis functions are still not calculating right...they seem to be keeping '1000' and '1001' in the calculation still...

[lucasdavis500]

what is the affect you're trying to achieve by adding FLOOR(NUM#(

[Anil_Babu_Samineni]

Might be by using internal

IF(ResolutionDate='Null',IF(MaxDateCreated-DateCreated>30, '1000', '1001'),interval(ResolutionDate-DateCreated)) AS ResponseTime