Adding a column in Crosstab report to calculate "lift"

[bikash1207]

Hello Everyone,

I am trying to create a Crosstab report having spends across merchant for two periods, one is benchmark period while other is promotion period. Definition of both periods are based on user selection.

My Crosstab currently looks something like this:

                Benchmark period        Promotion period
Amazon        10                                15
Uber             3                                  17
.....              ...                                 ...

I want to add a new column lift in above table, with formula, (promotion period/benchmark period-1). Since in the table, periods are calculated based on user selection (data has transaction dates, and based on user selection these dates are classified as promotion period or benchmark period), so i can't use column name directly. So I am using following approach:

1) Create a column Diff, with query running-difference([Spend] for [Merchant])
2) Create another column Data Item1 as, total([Spend] for [Merchant],[Period]) - [Diff]
3) Finally lift is calculated as [Diff]/[Data Item1]

Basis this I am expecting table to look something like this:
               Benchmark period        Promotion period      Diff        Lift
Amazon        10                                15                      5          0.5
Uber             3                                  18                    15           5
.....              ...                                 ...                     ...           ...

But my final table is populating with no values for Diff and Lift. I am not sure why it is happening, is this because the period which I am using in columns are not actually present in data but are calculated field?

Any thought/ideas/inputs from you folks is appreciated.

Thanks in advance