Crystal equivalent to Tableau's Lookup function

Question

I'm trying to recreate the table depicted below in Crystal Reports:

Sample data used to generate the table:

The table communicates information about 4 measures: Popsicle Sales, Anticipated Popsicle Sales (Budget), Soda Sales, and Anticipated Soda Sales (Budget). It displays the current month's values of Popsicle Sales and Soda Sales along with their prior year values, and it compares the variance of the current values to budgeted and prior year values.

In Tableau, I use a Calculated field that leverages Tableau's Lookup function ("LOOKUP(SUM(PopsicleSales), -12)") to obtain the value of PopsicleSales 12 months ago. How can I do the same with a formula field?

I found a post that suggested the following, but it returns 0 for me: if Month(DateValue({table.Date})) = Month({?ReportDate}) and Year(DateValue({table.Date})) = Year(DateAdd("m",-12, {?ReportDate})) then {table.PopsicleSales} — DarrylR
You probably won't be able to do it with just a Formula. Crystal evaluates things one at a time and almost "forgets" them when it moves on to the next record. You'll need a Running Total. — 4444
I tried using a Running Total on SourceData$.PopsicleSales with the following Evaluation formula (where 6/30/2016 is the report run date, which is passed as a parameter and used as the report selection criteria): {SourceData$.Date} >= DateAdd ("yyyy", -1, #6/30/2016#) However, it returned the value of PopsicleSales on 6/30/2016 instead of the value on 6/30/2015. — DarrylR
The Running Total idea suggested by 4444 worked after I modified the Evaluation formula and tweaked the report (see below). — DarrylR

4444 4444 · Accepted Answer · 2016-09-13T15:57:50

Use Running Totals in your report footer. These are like special summary fields where you can choose which items you want to include in the sum and which items to exclude.

In the Use a Formula section, put in something like this to only evaluate records between now and one year ago:

{SourceData$.Date} = DateAdd("yyyy", -1, {?ReportDate})

Crystal equivalent to Tableau's Lookup function

1 Answers