Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need to display KPIs at two different periods: monthly and weekly. I currently use the following formula in a filter pane to show either a month or week filter.
if(vTimePeriod=1,
Aggr(Only(FLT_DATE.Calendar.Month_MMMYYYY),FLT_DATE.Calendar.Month_MMMYYYY),
Aggr(Only(FLT_DATE.Calendar.WeekNo_Monday),FLT_DATE.Calendar.WeekNo_Monday)
)
vTimePeriod is a variable connected to a variable input with two buttons, where value=1 is Monthly and value =2 is weekly.
I want to allow only one selection at a time in the filter, so I've put it in checkbox mode.
I am having difficulty implementing two things, as stated below.
1. By default, the last (completed) month should be selected for the month filter. Similarly, the previous (completed) month should be selected for the week filter.
2. When we switch from month to week or vice versa the previous filter should be cleared and show the expected default selection.
If it is a chart, user set analysis to do this. Or maybe a Bookmark and set him by default
Alternate States
Hi,
I might be missing something in your requirement - but for:
1: This should be possible using a default dynamic bookmark selecting these values. If the KPI's default is month, then the dynamic bookmark should set latest closed month as a default selection. Could altenatively be set as a sheet action.
2: Use buttons to set the value of the variable, instead of variable input. These allow for multiple actions - including setting variable values and clearing selections etc.
Not sure I completely follow why the AGGR function is needed in you filter pane?
All the best,
Jan
Follow this,
// Set default selection for Month
DefaultFilterMonth:
LOAD * Inline [
Field, Value
FLT_DATE.Calendar.Month_MMMYYYY, =Date(Max(FLT_DATE.Calendar.Month_MMMYYYY))
];
// Set default selection for Week
DefaultFilterWeek:
LOAD * Inline [
Field, Value
FLT_DATE.Calendar.WeekNo_Monday, =WeekStart(Max(FLT_DATE.Calendar.Date), 0, 1)
];
Then create action buttons one for weekly and one for Monthly
Action for Monthly Mode:
Action for Weekly Mode:
Assign the actions to the respective buttons.
Update the filter pane expression, use the variable 'vTimePeriod'
=Aggr(
Only({<
FLT_DATE.Calendar.Month_MMMYYYY = {"=$(vTimePeriod=1)*"} >} FLT_DATE.Calendar.Month_MMMYYYY,
FLT_DATE.Calendar.WeekNo_Monday = {"=$(vTimePeriod=2)*"} >} FLT_DATE.Calendar.WeekNo_Monday)
)