Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi I have a filter Date_Worked. I have a KPI object that shows the sum of the HOURS_WORKED for the selected date. (That are reportable=y and excluded =n)
For the secondary KPI value I would like the Previous day from the selected filter. in the formula expression .
Current KPI with the Max Date Worked Selected- This works okay in the formual expression field
Sum({<Date_Worked={'$(=Max(Date_Worked))'},Reportable={'Y'},Excluded={'N'}>}HOURS_WORKED)
To get the previous day I have tried many iterations of -1. Creating date_worked a s a variable, trying to match the format.
Still I do not get any correct results just 0 appears.
Please can someone helped me ? I have also tried ....
Still no results....
Hi @andymayo
You have to make sure that your date is seen as a date first. Below is an example:
LOAD Date#(Date_Worked,'DD-MMM-YY') AS Date_Worked,
HOURS_WORKED,
Reportable,
Excluded
INLINE [
Date_Worked,HOURS_WORKED,Reportable,Excluded
01-Jan-20,1,Y,N
02-Jan-20,2,Y,N
03-Jan-20,3,Y,N
04-Jan-20,4,Y,N
05-Jan-20,5,Y,N
06-Jan-20,6,Y,N
];
Your formula for the max date selected will be:
=Sum({<Date_Worked={'$(=Date($(=Max(Date_Worked)),$(=chr(39))DD-MMM-YY$(=chr(39))))'},Reportable={'Y'},Excluded={'N'}>} HOURS_WORKED)
The expression for the day before the max date selected will be:
=Sum({<Date_Worked={'$(=Date($(=Max(Date_Worked)-1),$(=chr(39))DD-MMM-YY$(=chr(39))))'},Reportable={'Y'},Excluded={'N'}>} HOURS_WORKED)
Just replace the part in red with your date's format (which can be seen by putting Date_Worked into a filter pane).
Hope this helps.
Regards,
Mauritz
Awesome, Thanks very much.
Hi @andymayo
You have to make sure that your date is seen as a date first. Below is an example:
LOAD Date#(Date_Worked,'DD-MMM-YY') AS Date_Worked,
HOURS_WORKED,
Reportable,
Excluded
INLINE [
Date_Worked,HOURS_WORKED,Reportable,Excluded
01-Jan-20,1,Y,N
02-Jan-20,2,Y,N
03-Jan-20,3,Y,N
04-Jan-20,4,Y,N
05-Jan-20,5,Y,N
06-Jan-20,6,Y,N
];
Your formula for the max date selected will be:
=Sum({<Date_Worked={'$(=Date($(=Max(Date_Worked)),$(=chr(39))DD-MMM-YY$(=chr(39))))'},Reportable={'Y'},Excluded={'N'}>} HOURS_WORKED)
The expression for the day before the max date selected will be:
=Sum({<Date_Worked={'$(=Date($(=Max(Date_Worked)-1),$(=chr(39))DD-MMM-YY$(=chr(39))))'},Reportable={'Y'},Excluded={'N'}>} HOURS_WORKED)
Just replace the part in red with your date's format (which can be seen by putting Date_Worked into a filter pane).
Hope this helps.
Regards,
Mauritz
Awesome, Thanks very much.