I am working with a dataset that pulls in sales and inventory data for items on a daily basis. In data analyzer, I created a table with the data grouped by week. After grouping by week, most of the fields in my card (i.e. Revenue, Units Sold, etc.) are aggregated by SUM because I would like to know the sum of all of the daily values for the week.
There are other fields (i.e. Current Inventory On Hand, Inventory In Transit, etc.) where I would only like to show the value from the most recent date in each given week. If I aggregated these fields by SUM, the output is incorrect. If I aggregate these fields by "No aggregation", the data expands to show the daily amount for each day of the week.
Can anyone please tell me how I can show some fields as SUM and others as the most recent date available in an aggregated week? Can it be done through a beast mode? Magic ETL?
I attached an Excel document with sample data and a desired output for reference.