Good morning all,
I have this challenge where I have to show the sales for the most recent date within each category. I'm not being able to attach the workbook but it is from the SuperStore sample. Here's the solution:
So it's just sales by Category and Department, filtered out by the the Most Recent Date, i.e., { FIXED [Category] : MAX([Order Date]) } = [Order Date]
But now my question.
If I remove Category out of the view, sales value remains the same, which is supposed to happen, but if I remove Order Date, then the whole LOD thing falls apart. Why is this? If we have FIXED the view to Category, shouldn't it always show the correct values, despite what we have or don't have in the view?
Thank you and have a good day,
Esther
I think you can get what you are looking for with nested LODs, and no filters: NOTE: I am using 10.1 and it appears that they changed Department>>Category and Category>>Sub-category
Calculation:
{ FIXED [Sub-Category] : SUM(if { FIXED [Sub-Category] : Max([Order Date])} == [Order Date] then [Sales] end)}
AS measure
Nipun Garg is correct that Fixed keeps the calculation at a higher level of detail even if the view is lower:
if you want to show just the list of sales without category on the view: convert the above calculation to a dimension and put it on rows:
the values are in a different order because they are not sorted by sub-category but they are the same values
