+ Start a Discussion
Cthulhu42Cthulhu42 

help with PREVGROUPVAL

Hey-

 

I'm trying to create trending reports on analytic snapshots I've captured similar to the Opportunity Trend 3 report in here: http://www.salesforce.com/community/spring09/administrators/force-database/summary-rpt-data.jsp#. I'm pretty sure I need to create a custom summary field, but I'm not really sure how to do it. I can't seem to get the syntax right. Can someone point me at some examples?

 

Thanks!

werewolfwerewolf
Here's one example, not quite apropos to what you're doing, but it might be helpful.
Cthulhu42Cthulhu42
YES! It's not very much like what I'm doing, but it gave enough additional detail to help me figure it out. Thank you!!
kpezkpezkpezkpez

I have a similar issue, and the documentation on PREVGROUPVAL is spottyIt is new and that is understandable.

 

I am trying to create trailing twelve month (TTM) reports and corresponding dashboard chartsAs anyone who has used TTM (or TT weeks or days) charts knows, they are one of the best financial analytic tools available to a managerThe beauty of them is the way they remove seasonality from a business's financial resultsAs such, when they go up, it is goodWhen they go down, it is bad.

 

BACKGROUNDWe have a custom tab/object in salesforce called Financials into which we record our financial resultsThere is one record per period (week, month, quarter & year).  On each record, we have all the predictable metrics like revenue, expenses etcAccordingly, I can easily write a report that shows monthly sales results for the last 36 monthsOne column has the last date of the month and the other has the monthly sales figure like this:

 

Last Date     Sales

1/31/08       $200,000

2/28/08       $250,000

         .........

12/31/08     $225,000

 

CHALLENGE:   To create TTM charts, I need plot the SUM of the previous 12 months of sales results on a chart every monthThus, I think I need to do one of two things to achieve this in salesforceEither, 1) create a new field on each record which somehow refers to the previous period's record and the 10 before that and adds those figures to the most recent period's record; or 2) (more likely) use PREVGROUPVAL to refer to the previous periods on a report and SUM them together as described aboveThat might look like SUM of 12 different PREVGROUPVAL results using this form:

 

PREVGROUPVAL(summary_field, grouping_level [, increment]) + PREVGROUPVAL(summary_field, grouping_level [, increment]) and so on.

 

If implemented correctly, my resulting report would look as follows (with any luck I can get a Dashboard to graph the TTM results too).

 

Last Date     Sales          TTM

1/31/08       $200,000    $2,600,000 (the previous 11 months plus this one)

2/28/08       $250,000    $2,850,000 (jan ttm plus feb's results)

         .........

12/31/08     $225,000     $4,150,000 (the sum of the previous 11 months plus dec)

 

 

Two questions for the Community:

 

1Using PREVGROUPVAL, what would my grouping level beI don't seem to be able to figure that outSo far, I have this: PREVGROUPVAL(Sales, grouping_level [, 1]).  Is that suppossed to be -1,-2 etc?

 

2Am I thinking about this the right way/Is there a better (easier/more elegant) solutionI tried an analytic snapshot, but I got frustratedI tried a matrix report, same result.

 

In Excel, this is a 2 minute problem I know, and my fallback solution is just to do this manually... but what fun would that be???!!!! Plus, this is so easy to describe, I think it is possible somehow.

 

Thanks,

 

Ken

ErikTheTinkererErikTheTinkerer

1Using PREVGROUPVAL, what would my grouping level beI don't seem to be able to figure that outSo far, I have this: PREVGROUPVAL(Sales, grouping_level [, 1]).  Is that suppossed to be -1,-2 etc?

 

Group by Last Date value (hopefully that is a Date field in salesforce... if it is a text field, you'll have problems)

 

2Am I thinking about this the right way/Is there a better (easier/more elegant) solutionI tried an analytic snapshot, but I got frustratedI tried a matrix report, same result.

 

I think you are on track.

You will need to use the "Increment" feature of the PrevGroupVal function as you sum the previous 11 months toghether:

 

PREVGROUPVAL(Sales, grouping_level) + PREVGROUPVAL(Sales , grouping_level , 1) + PREVGROUPVAL(Sales , grouping_level , 2) .... all the way to .... + PREVGROUPVAL(Sales , grouping_level , 11)

Tas_SDFCTas_SDFC
This doesn't work by just adding the PrevGroupVal and changing the grouping. Did anyone have luck on getting this work?