topic Re: Last Week's Data in Table in QlikView

Last Week's Data in Table

marcusbohman — Sat, 16 Nov 2024 05:08:31 GMT

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?

Re: Last Week's Data in Table

tresB — Mon, 26 Nov 2018 08:39:28 GMT

Try correcting a bracket, like:

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

Re: Last Week's Data in Table

Peter_Cammaert — Mon, 26 Nov 2018 08:40:24 GMT

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

Re: Last Week's Data in Table

marcusbohman — Mon, 26 Nov 2018 08:43:57 GMT

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

Re: Last Week's Data in Table

marcusbohman — Mon, 26 Nov 2018 08:44:12 GMT

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

Re: Last Week's Data in Table

Frank_Hartmann — Mon, 26 Nov 2018 08:44:32 GMT

Maybe this:

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

Re: Last Week's Data in Table

marcusbohman — Mon, 26 Nov 2018 08:46:15 GMT

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

Re: Last Week's Data in Table

Peter_Cammaert — Mon, 26 Nov 2018 08:49:03 GMT

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?

Re: Last Week's Data in Table

marcusbohman — Mon, 26 Nov 2018 08:55:44 GMT

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