I'd like to have a bar chart by month showing the value for the month, but only of the max week of 4 or 5 weeks in the month. To get the bar chart by month I use this set analysis -
sum({<[Fiscal Year Numeric]={$(=max([Fiscal Year Numeric]))},[RentCal Month]=>}[Rent Account Core Arrears])
But I need to add some set analysis in for [RentCal Week]. If I use max as I have for the Fiscal Year above, I only get one week in the bar chart which isn't what I want. I'd like one week per month.
I tried to use firstsortedvalue([RentCal Week], [RentCal Month]) but that hasn't worked. I think I need to nest my set analysis for max week inside the month somehow, but not sure how to do this.
Oleg, I don't understand why you can't see the file... I checked when I wasn't logged in and it was ok for me then too. Can you see tresesco's file below? But don't worry too much. I think I can maybe sell the customer the Last Week in Month Flag. Going to try today anyway!
