Formula based on filter selection

#1

Hi everyone :slight_smile:
I have a scenario for a KPI dashboard where based on a month selected, I need to calculate the number of jobs that were classed as overdue at that point in time.
I have a dashboard with a filter which selects ALL dates based on a month selection, and this is necessary as there are other metrics on the dashboard which are filtering normally to only show for instance the number of jobs completed in said month etc.

However I need to write a formula such as
var SelectedMonth = GO GET THE MONTH SELECTED IN THE FILTER AND CALCULATE LAST DAY OF MONTH…OR IF MONTH SELECTED IS CURRENT MONTH GET YESTERDAYS DATE
if($targetdate$<=SelectedMonth&&
($completedate$>SelectedMonth||$completedate$==null)&&
$takendate$<=SelectedMonth);
return $JobOID$

The question is, can I ask a formula in a metric to look at the filter first and if so how? and then once I have done this can the parts in capitals work.

This is something I have managed in another reporting software, but I am struggling with a few elements as only been using Dundas for 6 weeks, and I also wonder if I am completely barking up the wrong tree with my approach :woozy_face:

As always any assistance would be really appreciated.

Thanks

Chris

#2

Hi Chris,

I don’t know much about formulas, but thru script you can have all the values you need like this:
//Single Date filter
var vp1 = this.parentView.control.getViewParameterByName(“viewParameter1”);
//gets the current date
var today = new Date();
//declare the variable for using it below
var date = new Date();
// gets the date set, the default value is set to Now, and it returns null as value
var selectedDate = vp1.parameterValue.value;

if (selectedDate === null)
{
// Sets the date to today
date = today;
}
else
{
// Sets the date to the one selected in the Single Date filter
date = selectedDate;
}
//gets the year in a yyyy format
var theYear = date.getFullYear();
//gets the current month (the base is 0 so you have to add 1)
var theMonth = date.getMonth() + 1;
//gets the day
var theDay = date.getDate();
//gets the full date of the day before the date set
var previousDayDate = new Date(date.getFullYear(),date.getMonth(), date.getDate() -1,23, 59, 59, 0);
//gets the last day of the previous month full date (if the current month is January it will return the 31 of December of the previous year)
var lastDayOfThePreviousMonth = new Date(date.getFullYear(),date.getMonth(), 0,23, 59, 59, 0);

I hope that helps.

Olivier

1 Like
#3

Hi Chris,

If the value you need is specifically in a filter or parameter, you can add a parameter to a formula by typing in $SelectedMonth$, and after applying you can change its type from a number to a date/time if you want to. If your existing filter & view parameter were selecting some date you’d have the option of connecting it directly to the date/time parameter under your formula measure. Otherwise, you could create a new view parameter connected to that parameter instead, and there may be some script needed somewhere like the Parameter Value Changed actions on your current filter to set the formula parameter, depending on its type.

2 Likes
#4

Piggybacking off Jamie’s suggestion of using a parameter placeholder within a formula, do be extra careful when setting up the filter, as a common muscle memory oversight in this situation can be to select the formula measure itself when establishing the connection. Make sure you expand the measure and select only the placeholder within, like so:

image
image.png354Ă—537 11.6 KB

#5

Thank you for everyones input - and also for Christian who spent some time with me to show how parameter setups worked and how to get everything in order