Previous Day based on Filer Selection

[andymayo]

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 ....

Try converting the max date to a date variable:

Sum({<Date_Worked={'$(=Date(Max(Date_Worked)-1))'},Reportable={'Y'},Excluded={'N'}>}HOURS_WORKED)

Or Using an if statement might work:

Sum({<Reportable={'Y'},Excluded={'N'}>} If(Date_Worked=(Max(TOTAL Date_Worked)-1), HOURS_WORKED, 0))

Still no results....

[Mauritz_SA · Accepted solution]

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

[andymayo · Accepted solution]

Awesome, Thanks very much.