Conditional Expression

[nsamuels]

I am trying to create a conditional that compares the decline in number by month through a pivot table. I'm not sure what expression I should use to analyze that. Please help!!! Thank you!

[bdbits]

You've said nothing about your data source. And since "pivot table" is not really a Cognos term, are you using a crosstab or are you accessing Cognos data in Excel or something else? What have you tried, and are you familiar with dimensional concepts? Have you consulted the Report Studio User Guide, which has some pretty good sections on dimensional reporting?

You're going to have to be a lot clearer about the particulars of what you are trying to do if you want someone to try to help.

[nsamuels]

Hello,

My data sources are Referring Doctor Name, Charge Transaction Count by Service Month. I meant to say crosstab in Cognos, I've tried the standard Conditional color by rank but I want to compare the Service Months by the Referring Doctor. If the count decreases by month I want it to show red. Is there an expression that I would need to put in lika an IF, THEN, Else function?

I hope this was clearer. Sorry for confusion.

[BigChris]

I can see what you're trying to do and I've been trying to think of an elegant wat of doing it. I'm sure there is one, but I can't think what it would be. So, here's a rather ugly but pragmatic possible solution. Instead of having a cross tab, you could create a list with a column for each month. You'd still have the Referring Doctor, but then you'd have columns that would be something like:
6 months: if([DateField] between _first_of_month(_add_months(current_date,-6)) and _last_of_month(_add_months(current_date,-6))) then ([Charge Transaction]) else (0)  -- that would give you 6 months ago
5 months: if([DateField] between _first_of_month(_add_months(current_date,-5)) and _last_of_month(_add_months(current_date,-5))) then ([Charge Transaction]) else (0)  -- that would give you 5 months ago

Then you could create a series of conditional formats. On the 5 months column you'd compare that to the 6 months column and colour it red if it was less than the 6 months column.

It would be a bit fiddly, but you should be able to make it work...

[nsamuels]

Thank you so much!!! Would I add the expression "if([DateField] between _first_of_month(_add_months(current_date,-6)) and _last_of_month(_add_months(current_date,-6))) then ([Charge Transaction]) else (0)" to a Data Item and name it for ex. "July" and then do the conditional?

[BigChris]

You certainly would, but you might want to do something less fixed than naming it July. The sort of thing I've done before is create that data item and call it "6 months ago" or something like that. Then I created another data item to get the name of the month:

Code Select Expand

substring('Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec', month( _add_months(current_date,-6))*4-3,3)

Then I've unlocked the report and put that data item into the Heading for the relevant column.

[nsamuels]

Okay, that worked!!Thank you, but not sure how you unlocked the report.

[nsamuels]

Also, what if I wanted to display July, August, September, October ect...the practice was only in buisness since July 2015.

[nsamuels]

The expression if([Month] between _first_of_month(_add_months(current_date,-4)) and _last_of_month(_add_months(current_date,-4))) then ([A/R Charge Count]) else (0) is putting out an error message stating   Cannot convert the string value 'Jul' to data type date.

[MFGF]

Okay, that worked!!Thank you, but not sure how you unlocked the report.

There is a button on the toolbar that looks like a padlock. Normally it shows in the "locked" position. If you press it once, it shows "unlocked" and you can drag stuff inside existing objects in the report. Press it a second time and it switches back to "locked" again.

The expression if([Month] between _first_of_month(_add_months(current_date,-4)) and _last_of_month(_add_months(current_date,-4))) then ([A/R Charge Count]) else (0) is putting out an error message stating   Cannot convert the string value 'Jul' to data type date.

I'm guessing that the [Month] item in your expression isn't a date? That seems to be what the error is complaining about...

MF.

[nsamuels]

I think I found an alternative way get the conditional expression to work. I included the average to the crosstab, so now the data sets that I am using are (Charge Transaction Count, Referring Doctore Name Full, C/R Service Month Name and Average. I tried the following expression, and, of course, I received errors.

If (([Referring Doctor Name Full].[Charge Transaction Count])<[Average])
THEN ('Red')
ELSE ('Green')

When I put the above expression definition I received the following error message:

RSV-VAL-0034 Failed to validate the variable Boolean1. CRX-API-0016 The expression input string is empty:""..

Do you have any suggestions?
Also, would the above expression be inserted into the advanced Conditional Style?

Thank you!