formula help please

[Former Member]

Hi All,

I have a report where I have two fields completed by and quarterly_review.For Q2 I have corresponding value for Quarterly_review columns as Approved(as below)

I kept four hard coded values q1,q2,q3,q4 in my report header section.

So whenever review happened or having a value(quarterly_review field) for the particular quarter I need to show the respected values for that.

otherwise it has to show the empty value.

I think we have to create a formula please suggest how to do

[abhilash_kumar · Accepted solution]

Hi Kalyan,

Easiest way to do this is to create a crosstab and palce it on the report header.

The Column field should be 'Completed By' and the Summary Field should be 'Quaterly Review' with the summary set to Maximum/Minimum.

-Abhilash

[abhilash_kumar · Accepted solution]

If you do not wish to use a Crosstab, create separate formulae for each column. An example for Q1 would be:

If {Completed_By} = "Q1" then

     {Quaterly_Review}

Go to Insert Summary > Choose this formula field as the field to summarize > choose 'Maximum' as the summary operation and place it on the Report Footer.

Do the same for the other columns.

-Abhilash

[Former Member · Accepted solution]

Hi Abhialsh,

I have many others fields in my report.This is just a part only.

Please find my entire report output.

Now in report Quarterly Review' has the value for Q4 which is in blue color.

So under Q4 header I need to show as approved and remaining as blank .

Please suggest

[abhilash_kumar · Accepted solution]

Create a formula similar to what I posted above > Insert a summary > choose maximum and place it on the Report Footer under the Q4 column.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

I have to show the formula in details section under all the hard coded headers(Q1,Q2,Q3,Q4).I think formula is different for all the 4 headers.

But the formula you gave above hard coded as Q1.

Please suggest I am confusing

[abhilash_kumar · Accepted solution]

Yes, for each of the quarters you'll need to create a separate formula as suggested above.

After creating the formula above, create another formula for each of the quarters. E.g for Q1 would be:

Maximum({@Formula_name_for_Q1})

Place this on the Details Section under Q1 column.

Do the same for all the remaining quarters.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

I did what you said.It is working now.

Can you please suggest what is the use of max function here

And also I did like this.This also working

if {FocusOnCustomer.COMPLETED_BY}='Q1'

and

not(isnull({FocusOnCustomer.QUARTERLY_REVIEW}))

then

{FocusOnCustomer.QUARTERLY_REVIEW}

[abhilash_kumar · Accepted solution]

Glad it works!

Since you only have one row for each quarter, the max function simply returns that value for each quarter.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

Some times I am getting multiple values too.

like this.

Q1           Approved

Q1           Approved

In that case what I have to do.

[abhilash_kumar · Accepted solution]

The Max function should take care of that as well.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

One last question what about the formula that I have written .What it will do in both the cases.

can you please suggest.

if {FocusOnCustomer.COMPLETED_BY}='Q1'

and

not(isnull({FocusOnCustomer.QUARTERLY_REVIEW}))

then

{FocusOnCustomer.QUARTERLY_REVIEW}

[abhilash_kumar · Accepted solution]

The formula will first check whether the Completed By is equal to Q1 and whether the Quaterly Review is Not Null; if both these conditions are met then the Quarterly Review field is returned.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

I am sorry the formula you suggested I am getting wrong results.

Now in db for Q2 I have corresponding value like this.

Q2          Q2Review

Q3          Null

But in my report I am getting like this.

Q2          Q2Review

Q3          Q2Review

But I don't want Q2review for Q3.Please suggest

[abhilash_kumar · Accepted solution]

For the Q3 column, you need to create a separate formula and a separate summary formula too.

-Abhilash

[abhilash_kumar · Accepted solution]

If you have multiple rows of data, then you can't place the summaries on the details section.

That's why I initially asked you to place them on the Report Footer.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

I did what you said.But still I am not getting the result

PFA report

test.txt.zip

[abhilash_kumar · Accepted solution]

What's wrong in the code?

What do you expect the Max formula to show on the second?

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

I want to show a blank value besides Q3  like below as we have data for only Q2.

CompletedBy       Q1           Q2                      Q3                Q4

Q2                                                    Q2Review

Q3          

[abhilash_kumar · Accepted solution]

Change each Quarter_Review formula to:

if {FocusOnCustomer.COMPLETED_BY} = "Q1" then

maximum({@Completed_Q1})

else if {FocusOnCustomer.COMPLETED_BY} = "Q2" then

maximum({@Completed_Q2})

else if {FocusOnCustomer.COMPLETED_BY} = "Q3" then

maximum({@Completed_Q3})

else if {FocusOnCustomer.COMPLETED_BY} = "Q4" then

maximum({@Completed_Q4})

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

I am really sorry for asking again and again.

You mean to say I have to  write same formula for all the four Quarter_Review formulas.

PFA I want exactly like this

[abhilash_kumar · Accepted solution]

I'm sorry, the formula should be:

if {FocusOnCustomer.COMPLETED_BY} = "Q2" then

maximum({@Completed_Q2})

else ""

For other columns, just change the Quarter numbers.

-Abhilash

[Former Member · Accepted solution]

Many thanks Abhilash,

It is working now

[Former Member · Accepted solution]

Hi Abhilash,

Sorry for restarting this thread.

Now in my db I have data like this.where One row has for Q1 has value and othere dont have.

But in my report I am getting like this.Please help