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: 
marcusbohman
Contributor II
Contributor II

Last Week's Data in Table

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?

Labels (2)
1 Solution

Accepted Solutions
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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?

View solution in original post

8 Replies
tresesco
MVP
MVP

Try correcting a bracket, like:

Week = {"$(=Year(Today()-7)*100+Week(Today()-7))"}

Peter_Cammaert
Partner - Champion III
Partner - Champion III

Add an extra right-parenthesis at the end of your search expression in the set analysis for Week, and it should work ok.

marcusbohman
Contributor II
Contributor II
Author

Well spotted... However, it still returns all rows.

marcusbohman
Contributor II
Contributor II
Author

Well spotted... However, it still returns all rows.
Frank_Hartmann
Master II
Master II

Maybe this:

=Sum({< Site = {'Gent'},Brand = {'VTB'}, Week = {"$(=Year(Today()-7)&week(weekstart(Today()-7)))"}, [Detailed Reason] -= {'Availability'}>} [No of BackOrder Hits])
marcusbohman
Contributor II
Contributor II
Author

Thank you, Frank! However all rows are still shown in the table.
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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?

marcusbohman
Contributor II
Contributor II
Author

Hi Peter,
Week is created in the script by year(Date)*100+week(Date) as Week so I would expect it to be an integer. Adding Num solved it! Thank you for the tip!
=Week = {"$(=Num(Year(Today()-7)*100+Week(Today()-7))"}