Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have Week as YYYYWW and I would like to have a table only showing the last week's result.
Below executes well but will only show last week's data until I have loaded something from this week. As I do daily loads this will not work during the full week
=Sum({< Site = {'Gent'},Brand = {'VTB'}, Week = {"$(=Max(Week ))"}, [Detailed Reason] -= {'Availability'}>} [No of BackOrder Hits])
Below is my attempt to only show the last week's data but it returns everything.
=Sum({< Site = {'Gent'},Brand = {'VTB'}, Week = {"$(=Year(Today()-7)*100+Week(Today()-7)"}, [Detailed Reason] -= {'Availability'}>} [No of BackOrder Hits])
Any suggestions?
In set analysis, the value in the set must match the value in the field, including the type. AFAIK expression searches return strings. Is Week also carrying strings?
To check it out, copy the expression from between $( and the final ) and put it in a text object on a sheet. Then compare to the values in field Week. Do they match?
Try correcting a bracket, like:
Week = {"$(=Year(Today()-7)*100+Week(Today()-7))"}
Add an extra right-parenthesis at the end of your search expression in the set analysis for Week, and it should work ok.
Well spotted... However, it still returns all rows.
In set analysis, the value in the set must match the value in the field, including the type. AFAIK expression searches return strings. Is Week also carrying strings?
To check it out, copy the expression from between $( and the final ) and put it in a text object on a sheet. Then compare to the values in field Week. Do they match?