Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
marcusbohman
New 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

Re: Last Week's Data in Table

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?

8 Replies
MVP
MVP

Re: Last Week's Data in Table

Try correcting a bracket, like:

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

Re: Last Week's Data in Table

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

marcusbohman
New Contributor II

Re: Last Week's Data in Table

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

marcusbohman
New Contributor II

Re: Last Week's Data in Table

Well spotted... However, it still returns all rows.
Frank_Hartmann
Honored Contributor

Re: Last Week's Data in Table

Maybe this:

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

Re: Last Week's Data in Table

Thank you, Frank! However all rows are still shown in the table.

Re: Last Week's Data in Table

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
New Contributor II

Re: Last Week's Data in Table

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))"}