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
Try something like:
IF (won)
THEN ('A')
ELSE (NULL)
And then supress the zeros in the list.
Regards,
Bark
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
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.
that is awesome thanks....exactly what I was trying to do to
Thanks so much