Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
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.
Any help would be great,
thanks very much, Charlotte
I have resolved by using this answer to a previous question from swuehl. But any other answers welcome...
It seems to me that your maximum DATA per MES and PRODUTO might be static (not depending on selections), so you could add a flag to your data model (add these lines to your script):
LEFT JOIN (Calendario) LOAD
MES,
PRODUTO,
max(DATA) as DATA,
1 as MAXDATAFLAG
Resident Calendario group by MES,PRODUTO;
and use this as expression in your chart with dimensions MES and PRODUTO:
=sum({<MAXDATAFLAG={1}>} VALOR)
But in fact my solution above is not going to work because the week I display within the month should be the max week selected. That is if no week selected then - max week in month, otherwise use the week selected. But always display only one week within the month. Thanks for any advice
Charlotte,
you should always keep in mind that the Set Analysis condition cannot be sensitive to Dimension values, because it's being calculated globally once per chart.
In your case, the requirement is the following:
1. If Week number is selected, then show the selected week, or
2. Otherwise use the last week of each month.
Both selections can be expressed in a way that doesn't depend on the Chart Dimensions, so your goal is achievable.
I'd recommend creating a variable that stores the desired condition, and using the variable in Set Analysis as a Modifier. Something along the following lines (can't guarantee the exact syntax):
SET vWeekFilter = '=if(GetSelectedCount(Week) > 0, 'Week = {$(=max(Week))}', 'LastWeekFlag={1}') ';
(in this expression, if any selections of Weeks were made, we use the maximum available week, otherwise we use those weeks marked with the Flag=1)
The chart expression will look like the following:
=sum({<$(vWeekFilter)>} VALOR)
I'm preparing a new blog post on using variables in dynamically calculated expressions, stay tuned and check my blog side:
Oleg Troyansky
Hi Oleg,
Thanks very much. I read your blog too - really useful. However I can't get the syntax to actually work! I attach my file, based on the example in your blog. If you have a moment, be most grateful if you could have a look...
thanks
Charlotte
Hi Charlotte,
I can't see your file - can you try attaching it again?
Oleg
Here it is... Let me know if that's not ok.
Nope, can't see any attachments...
Hi Charlotte,
Try to use this formula for your sample QVW:
=sum(if(GetSelectedCount(Week) > 0,DateCounter,if(Week(MonthEnd(Date)) = week(Date),DateCounter,0)))
If to your formula in your first message, it becomes
=sum({<[Fiscal Year Numeric]={$(=max([Fiscal Year Numeric]))},[RentCal Month]=>}if(GetSelectedCount([RentCal Week]) > 0,[Rent Account Core Arrears],if(Week(MonthEnd(Date)) = week(Date),[Rent Account Core Arrears],0)))
I don't know what's the name of your DATE field, just replace the above Date with your date field's name.
Hope this help.
Herman
May be like this?
PFA