How to create a variable to calculate the difference between two months' sales in WebI?

[former_member649523]

I want a column that calculates the difference between two months' sales.

For eg:

October $200
November $300 - $100 (300-200)
December $350 - $50 (350-300)
January $500 - $150 (500-350)
February $500 - $0 (500-500)

Thank you.

[mgrackin · Accepted solution]

Look at the Previous function. Here's an example using the eFashion universe:

=[Sales revenue] - Previous([Sales revenue])

[former_member649523 · Accepted solution]

Thank you Michael. This is perfectly working fine in a horizontal column. But not in Crosstab.

Crosstab:
Columns: [Year]
Rows: [Month]
Body: [Net Sales] and [Difference between two months sales]

Any idea/suggestion how to use previous function in crosstabs?

[DellSC]

How you do this will depend on the data. If the data is already aggregated at the month level, you can sort by date and use previous as explained above. If the data has not been aggregated, it will be a little more complicated. It will take several formulas and might look something like this:

{@InitVar}
NumberVar lastmonth := 0;
"";

{@CalcLastMonth}
WhilePrintingRecords;
NumberVar lastmonth := Sum({MyTable.Amount}, {MyTable.SaleDate}, 'Monthly'); 
""

{@Difference}
WhilePrintingRecords;
NumberVar lastmonth;
Sum({MyTable.Amount}, {MyTable.SaleDate}, 'Monthly') - lastmonth

The formulas will be used like this:

1. Group your report by the sale date and set it to show monthly.

2. Suppress the details section.

3. Put {@InitVar} in the Report Header section if the sale date is the only group. If there is another group outside of the date, put {@InitVar} in that group header. The formula will not display anything (which is the purpose of the empty string on the last line.

4. Put the month name, sum of sales for the month, and {@Difference} in the sale date group header.

5. Put {@CalcLastMonth} in the sale date group footer. If you don't need to display anything else there, you can either suppress the section (the formula should still run) or make the field as short as possible, position it at y=0, and make the section as small as possible.

This should get you what you're looking for.

-Dell

[former_member649523]

Hello Dell:
The data is aggregated and what Michael said works fine. But when I try to use this is crosstab, it fails.

Crosstab:
Columns: [Year]
Rows: [Month]
Body: [Net Sales] and [Difference between two months sales]

Any idea how to use previous function in crosstabs?

[DellSC]

You can't use "Previous()" in a cross-tab. Instead, take a look at creating a new summary in your cross-tab. In the formula editor you'll see "Grid Value" functions which will help you pull data from other rows/columns/cells in the cross-tab. Because the summaries automatically take place, you don't have to use any of the summary functions in these folders.

-Dell