Ranking on a Formula Field 1

[Fsyeda]

Hello,

I wanted to know how is it possible for me to Rank on a Formula Field. I have three columns : Sales Rep Name; Sales Variance ; and Margin Variance
I have calculated the sales and margin variance and used it in the report. Now I need to arrange the report in Descending order of the Sales variance.
Also, I have two Groups in my report ; one on Sales rep Name and other on Fiscal year.

Let me know if I need to provide further details.

 

[IanWaterman]

Provided the formula do not use any run time summaries then you can sort by these formula.

If they are sumaries you will need to do a TopN sort, just set N to a very high number and whole report will be sorted

Ian

 

[Fsyeda]

I do use run time summaries. can you provide me the details of the steps , since I have tried to get rank but that option is not visible for the formula field(Sales variance) at all.

Salesrep1
---------------------------------------SalesTotal------------MarginTotal
-----------------------------2013--------1000-----------------200 [These are hidden]
-----------------------------2012--------3000-----------------700 [These are hidden]

----------------Variance:--------------(-2000)-------------- (-500)[ I wish to sort the sales rep basis this Sales Variance for performance check]

Salesrep2
---------------------------------------SalesTotal------------MarginTotal
-----------------------------2013--------5000-----------------200
-----------------------------2012--------3000-----------------100

-------------Variance:-----------------(2000)-------------- (100)

 

[MCuthill]

Fsyeda,

TopN is an option in the "Group Sort Expert" dialogue box, and functions in the same manner as a sort except with a limitation as to the number of results visible (the entire report still runs in order to determine the TopN results). As Ian stated, you will want a very high value for N, as you wish the entire report to display, but sorted.

Ex: If there are 50,000 "groups" and N=100, the top 100 groups will be visible on the report.

Hope this helps! Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[Fsyeda]

When I click on Group Sort expert ,It does not show me the formula field to sort on. It shows me report fields (Tablename.attributes/measures) from the Table.

I need to sort basis the formula field which I have defined in the report.

 

[pmax9999]

How did you calculate the variance figures?

This question seems to be related to an earlier one where you were seeking assistance on calculating the variance and it is not clear how you resolved that issue. If you took the approach suggested by a couple of us and used variables to achieve it you are not going to be able to rank them (or at least not easily from within CR).

Perhaps if you describe in more detail how the variance is calculated we may be able to assist.

Also, are you open to different ways of displaying the data, such as 2012 in one column and 2013 in another, or are you committed to staying with the structure you describe above?

Cheers
Pete

 

[Fsyeda]

Pete,

I tried your code too, it works fine with my report. I am not committed to the structure. At the end of the day the report should have these fields(I hide the FiscalYear group and sales and margin detail)

SalesrepName ; SalesVariance and MarginVariance
With the SalesVariance descending

Also, there is one major issue with the report, I have Fiscalyear in my table but the request I got is YTD.

Basically Comparing the YTD salesvariance and marginvariance for all the salesrep and sending out the report every 1st day of the month.

 

[pmax9999]

Thw approach I took to achieve this is as follows.

Remove Year group.

Create 5 Formula and place them in details section (can be suppressed):

{@Sales2012}

If      {Table.Year} = 2012
Then    {Table.Sales}

{@Sales2013}

If      {Table.Year} = 2013
Then    {Table.Sales}

{@Margin2012}

If      {Table.Year} = 2012
Then    {Table.Margin}

{@Margin2013}

If      {Table.Year} = 2013
Then    {Table.Margin}

{@MarginNet}

If      {Table.Year} = 2012
Then    - {Table.Margin}
Else    {Table.Margin}

Create 2 formula and place them in the Group 1 (Salesperson) footer:

{@Sales Variance}

Sum({@Sales2013}, {Table.Salesperson}) - Sum({@Sales2012}, {Table.Salesperson})

{@Margin Variance}

Sum({@Margin2013}, {Table.Salesperson}) - Sum({@Margin2012}, {Table.Salesperson})

Create a "Sum" Summmary of {@MarginNet} in GF1 and hide field.

Do a Top N Sort of {Table.Salesperson} based on the Sum({@MarginNet})


Hope this helps

Cheers
Pete

 

[Fsyeda]

Oh well, I tried in the above suggestion and it worked fine with regards to the numbers. Thank you for that PETE .
As specified earlier, I need this report to be an YTD report generated every 1st of the month.

I talked to the DBA and added in the Calendar Year instead of Fiscal Year. I created a prompt for dates to be filled in (Multiple and Ranges). But how can I make it YTD calculation generated by itself?

 

[pmax9999]

You will need to define exactly what you mean by YTD. Calendar Year I understand as being Jan - Dec, but Fiscal Year is less standard. For example here in Australia the 'standard' tax year is July to June, but some companies run Jan to Dec, Oct to Sep etc. In addition to this, please advise whether the {Table.Year} field in your database represents the Calendar or Fiscal year - it is possible to calculate one from the other, but the calculation depends on what your Fiscal Year is.

Then, please explain in more detail exactly what results you are looking for. From your post I would understand that you want to see the same period from both years, not the full 2012 year and only part of the 2013 year - is that correct? If my understanding is correct, then it would be simply a matter of manipulating the record selection formula to return the specific months from each of the years.


Cheers
Pete

 

[Fsyeda]

A 2012 YTD will be sales from 01/01/2012 till today. My table has both Calendar and Fiscal. That is not a concern now. And Fiscal for our company is Oct-Sep.

Yes, You are right , I need to compare the same time period and not the complete 2012. Example
Sales Variance: [Sales in range ( 01/01/2013 - 07/26/2013 )] minus [Sales in Range(01/01/2012 - 07/26/2012)]

 

[pmax9999]

So the starting point would be to limit the records returned (ie via the Record Selection) to those between those 2 date ranges.

Assuming your Database includes actual dates, create the following formula to calculate the start and end date of each period:

[b][u]Yr1_From[/b][/u]
Date(Year(CurrentDate)-1,1,1)

[b][u]Yr2_From[/b][/u]
Date(Year(CurrentDate),1,1)

[b][u]Yr1_To[/b][/u]
Date(Year(CurrentDate)-1,Month(Currentdate),Day(CurrentDate))

[b][u]Yr2_To[/b][/u]
CurrentDate

Then amend your record selection to:

{Table.Date} in [Yr1_From to YR1_To] or
{Table.Date} in [Yr2_From to YR2_To]

I would also tweak the formulas I suggested previously so that the Years are calculated rather than hardcoded. For xample, rather than Hardcoding a formua for {@Sales2012} I would call it {@SalesYR1} and code it as follows:

If      {Table.Year} = Year(Currentdate) -1
Then    {Table.Sales}

Similarly, instead of {@Margin2013} I would call it {@MarginYR2} and code it as follows:

If      {Table.Year} = Year(Currentdate)
Then    {Table.Margin}

Hope this helps
Pete

 

[Fsyeda]

The report worked perfectly and it is exactly the way I wanted it to be.

Thank you so much Pete !

 

[pmax9999]

Glad it worked.

Cheers
Pete