Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
andymayo
Contributor III
Contributor III

Previous Day based on Filer Selection

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

Labels (3)
2 Solutions

Accepted Solutions
Mauritz_SA
Partner - Specialist
Partner - Specialist

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

View solution in original post

andymayo
Contributor III
Contributor III
Author

Awesome, Thanks very much.

View solution in original post

2 Replies
Mauritz_SA
Partner - Specialist
Partner - Specialist

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
Contributor III
Contributor III
Author

Awesome, Thanks very much.