Logic to show other figures than selected and the dimensions in table
I need to create a Pivot table that shows the Sales and the Sales from last TimePeriod. In the table I have the Time Period as a dimension and I need to be able to select specific time periods in list boxes.
The problem is illustrated like this:
Current Selections:
Period: 22
Week: 01
Pivot Table:
Period & Week; Sales; Sales Last Period
2201; Sum(Sales); Sum(????)
I need to figure out what to put in the formula with the question marks. Further I have attached a simple document to illustrate the problem.
Hope that someone can help me with this issue - and let me know if I should provide more details.
The problem is that my script is way more complicated than the example illustrated. I got like 80 million sales transaction allocated to a lot of different dimensions like items, size, color, barcode, transactionid, store, department, departmentgroup, staffid, terminal, time of sales and a lot more.
Further i need to be able to select e.g. the sales from the period minus x combined with the week minus y (and "in real life" i need to be able to perform this "twisted logic" to a lot of different fields). So the needed combinations are "endless" - so i would rather aviod having to define the logics in the script.
Further i need to be able to compare 2 different values in the expression. E.g. index figures of Sales LY with the field "Year" as a dimension. So i both need to get the sales according to the selection and dimension and the sales according to a different sets of criterias.
