Best Of

The first thing to check is to make sure that the ETL sees your due date and payment date fields as dates. If they aren't you can fix that a couple different ways: on the input dataset, you can click on configuration and then expand data handling and set the data type as date. You can also wrap the DATE() function around each of those fields in your formula, which will make them date values as well. If it doesn't let you change the type, then you might have some non-date data that you need to fix.

If they are all dates and you are still getting error, I have sometimes had to close the ETL and go back in and then click run preview to get the ETL to properly recognize the data and the new fields.

You could use the date range filter to show only last week.

You could also create a beast mode that would tell you if the date is within the previous week.

CASE WHEN `date` >=
/* first day of the previous week */
DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK)
     , INTERVAL (DAYOFWEEK(CURRENT_DATE()) - 1) DAY)
AND `date` <=
/* last day of the previous week */
     DATE_ADD(DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK)
     , INTERVAL (7- DAYOFWEEK(CURRENT_DATE())) DAY)
THEN 'Y'
ELSE 'N' END

Add this beast mode to your filter and filter to Y

Yes, you can use the rank & window and the lag function to get the previous value for each product and then use the formula tile to place it in your main column list.

However, first you are going to need to get a complete list of dates. You could get a distinct list of dates from your existing dataset if you know that there was activity by something on every date. Or you could bring in the date dimensions dataset. you would then join your complete list of dates to your main dataset with a left join so that you have a date for every day for each product. After that, you can do the rank & window with the lag. In your formula tile, you can utilize ifnull to populate your main columns with the lag value.

Hope this all makes sense.

I would suggest adding another group by that aggregates by body part and then join it back to your initial group by. You can then use the formula tile to calculate the percentage. If you are unaware, you can have multiple lines go out from any tile so that you can perform different operations such as this and then join them back together.