Set Analysis with dates

[bobbydave]

i am creating an application that takes this last week details. (Fiscal Year Last Week).

When i hardcode in the last week being 2017-38 in set analysis, I will get the value.

But I dont want to hardcode; i want it to realise the fiscal week last week.

The first text box shows the figure using set analysis and hardcoded date.

The second box is what i am trying to achieve which is 0 at the moment

The third box is another step i am trying to do which is get the range between last week and 6 weeks previous.

I dont want to use MaxString as

1. it doesnt work for me (that, or i am writing it inorrectly)

2. I need the 'last week' figure to be able to use for third box expression.

[sunny_talwar]

Try this

=Count( {<Country -= {'Korea', 'Latvia', 'Northern Ireland'},

[Weekly Control Description] -= {'Avaya'},

[The Fiscal Year Last Week] = {'$(=MaxString([The Fiscal Year Last Week]))'}

>}

[Weekly Record Count])

[bobbydave]

That's all good using MaxString but I wanted to use that expression in my 3rd box when I was to say

The Last 6 Weeks - Last Week

and by using MaxString, it wont give me a value if i were to use it that way

[sunny_talwar · Accepted solution]

May be this

=Count( {< Country -= {'Korea', 'Latvia', 'Northern Ireland'},

[Weekly Control Description] -= {'Avaya'},

[The Fiscal Year Last Week] =  {"=Only({1} [The Fiscal Year Last Week]) <= Max(TOTAL [The Fiscal Year Last Week]) and Only({1} [The Fiscal Year Last Week]) >= (Max(TOTAL [The Fiscal Year Last Week])-36)"}

>}

[Weekly Record Count])