Maybe an If Then Else Question

[bpothier]

I have a field that has the following values   won, lost, active. I would like to perform a calculation like this   if field = 'won' then (closed_date-open_date) - basically to get the cycle time of a won record. it is the else that is messing me up...I basically want to not return any rows is the 'if' is not met.    I know I could do this by filtering the data to only be 'won' records but in another column I would like to have close rate for which I woudl require the other values.



stumped

[Bark]

Try something like:

IF (won)
THEN ('A')
ELSE (NULL)

And then supress the zeros in the list.

Regards,

Bark

[bpothier]

thanks for the reply. I tried that but the null value is only in that one column. The other columns in the list will have data in them and I cannot find a way to supress based one 1 field. I can only get it to supress is all the fields in the row are null

[CognosPaul]

Can you sketch out what you're trying to show?

The impression I'm getting is you want to show something like this:

Attribute |  Win Cycle | Lose Cycle | Continuing
----------+------------+------------+--------
A         |      5     |    1       | --
B         |      4     |    1       | --


In this case the Win, Lose, and Continuing could all be considered calculated measures. Are they additive?

Try doing:
Win Cycle: case when [Field] = 'won' then _days_between([Open Date],[Close Date]) else 0 end
Lose Cycle: case when [Field] = 'lose' then _days_between([Open Date],[Close Date]) else 0 end
Continuing: case when [Field] not in ('won','lose') then _days_between([Open Date],current_date]) else 0 end

Set the aggregate functions to total and drag those in instead of field.

[bpothier]

that is awesome thanks....exactly what I was trying to do to

Thanks so much