I am having difficulties with the following problem:
I have sales data for (a lot) of dates and for 2 stores (but I want to make it expandable to n stores).
I want to create the following (pivot) table:
For each store, the date on which it sold the most. For that date, the amount sold in both that store and the other stores
I started down the road of using the calculated dimension =Date(Aggr(FirstSortedValue(Date1,-Sales),StoreDim1)), which does give the correct date.
But then I need to do something like "I know I am in the row for store 1 but give me the sales data for this date for store 2"
I have imported all the data in in two distinct tables (with distinct names) so I can have dimensions StoreDim1 and StoreDim2, in case that can help (so Pivot table has both dimensions to get the 'cross' cells).
Any ideas much appreciated!
