Hi all,
This is an urgent requirement, any help on this is much appreciated.
Attached is the screenshot of the data format that is available and the table I need to create from it as shown.
As an example :
Column Distinct weekly mode price is the distinct mode of price across all weeks. ex: 10 is the mode price for week 1 & week 4. 6 is the mode price for week 2 and similarly 5 is the mode price for week 3.
Column Avg of Price across weeks in which the given mode price falls. ex: 10 is mode price across week 1 & 4. Therefore we need to show, avg of all prices for week 10 & 4 which would be 8.75 as shown in the attachment. Similarly, 6.25 is the avg of all prices across week 2, since mode price 6 falls only in week 2.
Column Count of Distinct weeks the mode price is at. ex: mode price 10 falls in 2 distinct weeks (week 1 & week 10), hence the count would be 2. Likewise for others.
Thanks.
