

2. Re: Five week filter report
David Maning Jul 26, 2018 1:41 PM (in response to Tony Smith)Hi,
Here is the answer:
IF
(INT([BBCA Week In Fin Year]) >
{MIN(IF
DATETRUNC('week', NOW())
=
{ FIXED [BBCA Week In Fin Year]:MIN(
IF DATETRUNC('day', NOW()) = DATETRUNC('day', [Report Date])
THEN DATETRUNC('week', NOW())
END)}
THEN INT([BBCA Week In Fin Year])  5
END)}
AND INT([BBCA Week In Fin Year]) <=
{MIN(IF
DATETRUNC('week', NOW())
=
{ FIXED [BBCA Week In Fin Year]:MIN(
IF DATETRUNC('day', NOW()) = DATETRUNC('day', [Report Date])
THEN DATETRUNC('week', NOW())
END)}
THEN INT([BBCA Week In Fin Year])
END)})
OR
(INT([BBCA Week In Fin Year]) >
{MIN(IF
DATETRUNC('week', NOW())
=
{ FIXED [BBCA Week In Fin Year]:MIN(
IF DATETRUNC('day', NOW()) = DATETRUNC('day', [Report Date])
THEN DATETRUNC('week', NOW())
END)}
THEN INT([BBCA Week In Fin Year])  105
END)}
AND INT([BBCA Week In Fin Year]) <=
{MIN(IF
DATETRUNC('week', NOW())
=
{ FIXED [BBCA Week In Fin Year]:MIN(
IF DATETRUNC('day', NOW()) = DATETRUNC('day', [Report Date])
THEN DATETRUNC('week', NOW())
END)}
THEN INT([BBCA Week In Fin Year])  100
END)})
THEN 'show'
END
pls find update worksheet attached.
Trust this helps.
D

FiveWeeks_Formula_T1.twbx 617.4 KB


3. Re: Five week filter report
Tony Smith Jul 26, 2018 2:34 PM (in response to David Maning)Hi David,
Thank you for all your help.
I got the error message when I tried to open the attachment:
Errors occurred while trying to load the workbook. The load was not able to complete successfully.
I am using version 10.5. Can you please re attach the files.
Regards
Tony

4. Re: Five week filter report
Tony Smith Jul 26, 2018 2:58 PM (in response to Joe Oppelt)Hi Joe,
Yes, you are right. The current week is 1921, however I want to see data for a complete week and week 1921 has not yet completed. If I run the report on the 29th July 2018 when we will have completed week 1921, the current week will be 1921.
Thank you
Regards
Tony

5. Re: Five week filter report
Joe Oppelt Jul 26, 2018 3:09 PM (in response to Tony Smith)OK, Tony. So one way or another you know the week you need to grab. And your data has a really nice field [BBAC Week in fin...] to guide you to the correct data.
Regarding that field, if there is no reason for it to be a string field, it would be more efficient to make it numeric. Either do that in generating the data source initially, or make a calc that looks like this:
INT([BBAC Week...])
So lets say you can determine that you need 1920. Therefore you need 1920 back to 1916.
Make a calc that holds the last week you need. Call it [final week].
Now make this calc:
If ([BBAC Week Int] <= [Final week] and [BBAC Week Int] >= [Final week]4)
or
([BBAC Week Int] <= [Final week]100 and [BBAC Week Int] >= [Final week]  104)
then 1 else 0 END
Put that on filters. Select for value = 1. You'll get only the weeks you need.

6. Re: Five week filter report
David Maning Jul 26, 2018 3:11 PM (in response to Tony Smith)Pls find the updated workbook.
Considering your last update regarding the week the formula can easily amended to include this condition.
D

FiveWeeks_Formula_T1.twbx 606.1 KB


7. Re: Five week filter report
Joe Oppelt Jul 26, 2018 3:28 PM (in response to Joe Oppelt)See attached. Sheet 5. Put that filter calc on filters and select for 1.

FiveWeeks_Formula_T1 A.twbx 617.8 KB


8. Re: Five week filter report
Tony Smith Jul 26, 2018 3:58 PM (in response to Joe Oppelt)Hi Joe,
In your code I can see  100 and  104 in it. Can you please explain to me what the purposes of these numbers are? This is just for my understanding. Thank you for all your help
Thank you
Regards
Tony

9. Re: Five week filter report
Tony Smith Jul 26, 2018 11:21 PM (in response to David Maning)1 of 1 people found this helpfulHi David,
Using the code I can see week 1921 data. Based on the BBCA calendar this week is not yet completed. I expect to see week 1921 when I run the report on 29th July 2018. We only refer to a current week when we have a completed week.
Also, I can see  100 and  105 in your code. Can you please explain to me what the purposes of these numbers are? This is just for my understanding. Thank you for all your help
Regards
Tony

10. Re: Five week filter report
David Maning Jul 27, 2018 6:42 AM (in response to Tony Smith)Hi Tony,
In order to catch the condition of BBCA week determination based on max date in this week use this calculation:
{MIN(
IF DATETRUNC('day', NOW()) = { FIXED [BBCA Week In Fin Year]:MAX(DATETRUNC('day', [Report Date]))}
THEN INT([BBCA Week In Fin Year])
ELSE
IF DATETRUNC('day', NOW()) = DATETRUNC('day', [Report Date])
THEN INT([BBCA Week In Fin Year])  1
END
END
)}
The previous calculation is amended correspondingly:
IF
(INT([BBCA Week In Fin Year]) >
{MIN(IF
DATETRUNC('week', NOW())
=
{ FIXED [BBCA Week In Fin Year]:MIN(
IF DATETRUNC('day', NOW()) = DATETRUNC('day', [Report Date])
THEN DATETRUNC('week', NOW())
END)}
THEN INT([BBCA Week In Fin Year Determination])  5
END)}
AND INT([BBCA Week In Fin Year]) <=
{MIN(IF
DATETRUNC('week', NOW())
=
{ FIXED [BBCA Week In Fin Year]:MIN(
IF DATETRUNC('day', NOW()) = DATETRUNC('day', [Report Date])
THEN DATETRUNC('week', NOW())
END)}
THEN INT([BBCA Week In Fin Year Determination])
END)})
OR
(INT([BBCA Week In Fin Year]) >
{MIN(IF
DATETRUNC('week', NOW())
=
{ FIXED [BBCA Week In Fin Year]:MIN(
IF DATETRUNC('day', NOW()) = DATETRUNC('day', [Report Date])
THEN DATETRUNC('week', NOW())
END)}
THEN INT([BBCA Week In Fin Year Determination])  105
END)}
AND INT([BBCA Week In Fin Year]) <=
{MIN(IF
DATETRUNC('week', NOW())
=
{ FIXED [BBCA Week In Fin Year]:MIN(
IF DATETRUNC('day', NOW()) = DATETRUNC('day', [Report Date])
THEN DATETRUNC('week', NOW())
END)}
THEN INT([BBCA Week In Fin Year Determination])  100
END)})
THEN 'show'
END
I used 105 and 100 in order to catch this difference in periods which is equal to one year.
Pls mark my response as correct and helpful in order to close this thread.
Updated worksheet is in the attachment.
Trust this helps.
D

FiveWeeks_Formula_T1.twbx 593.3 KB


11. Re: Five week filter report
Joe Oppelt Jul 27, 2018 8:03 AM (in response to Tony Smith)1 of 1 people found this helpfulTony Smith wrote:
In your code I can see  100 and  104 in it. Can you please explain to me what the purposes of these numbers are? This is just for my understanding. Thank you for all your help
In your "Transaction Data" sheet you show BBCA year 19, with BBCA Weeks of 1916 through 1920. ANd for year 18 you show BBCA weeks of 1816 through 1820.
In my sheet 5 I made an integer equivalent of your BBCA Week field. (See [BBCA Week Int].)I also calc'd a [Final Week]. Look at the logic in there. For now I just subtracted one from the BBCA Week associated with whatever TODAY() is. It calcs as 1920 today.
Then I made [Filter Weeks]. It is setting a 1 or 0 for the rows in the weeks I want to grab. My goal is to collect 1816through1820, and 1916through1920. In one "or" section of that calc I grab 4 weeks earlier than [Final Week] through [Final Week] (which today are 1916through1920). In the other "or" section I grab weeks that are the same range, minus 100. (1816through1820). So I grab value from negative4 through zero in one half of the logic, and negative104 through negative100 in the other half. This works for me by virtue of changing the original BBCA Week strings to integers.
You will see on Sheet 5 that all the rows within those two ranges have a value of 1 for my [filter weeks] calc. All other rows have zero. When you put that calc on FILTERS and select for value=1, you'll just get those two stretches of target rows.

12. Re: Five week filter report
Tony Smith Jul 27, 2018 10:39 AM (in response to Joe Oppelt)Hi David /Joe,
Thank you for your help on this query and your explainations on the solution. I really appreciate it. Both solutions give me what I wanted.
Regards
Tony