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