4 Replies Latest reply on Feb 1, 2019 6:22 AM by Jim Dehner

# Choose a date and cut the data by specified number of weeks

Hello

I was wondering if someone could help

I'd like to be able to :

- choose a date in a parameter (filter)

- specify a number of weeks so that the Date for MAT will be able to cut the data in the following way, e.g.

Example of what I'd like to do:

You choose: 1509/2018 from 'Choose a date'

You select: 7 weeks from '# Weeks'

In the table I would like to see calculations for:

15/09-4/08 (Latest MAT)

28/07-16/06 (Previous MAT)

etc

Note: 29/09 is the latest date with data

ISSUE: At the moment, the calculation still counts the period from the latest date available (to the one selected in 'choose a date' i.e. 29-22/09/2019) and also counts 8 weeks in the Latest MAT.

CALCULATION:

This calculation is driven by 2 parameters (onde date and the number of weeks - integer)

Calculation script

Any help would be helpful

• ###### 1. Re: Choose a date and cut the data by specified number of weeks

hi Ewa

you are using datediff and your assumption is that all the differences will be positive

i.e. the first clause looks at week differences less or equal to 7

but the 2 weeks that are after the choose a date will return -1, and -2 which are also less than 7

you need to include a clause that eliminates negative values

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Choose a date and cut the data by specified number of weeks

Hi, Ewa

Try this

if Datediff('week',[Date],[Choose a date]) < 0 then 'Future MAT'

elseif  Datediff('week',[Date],[Choose a date]) < [# Weeks] then 'Latest MAT'

elseif  Datediff('week',[Date],[Choose a date]) < [# Weeks]*2 then 'Previous MAT'

elseif  Datediff('week',[Date],[Choose a date]) < [# Weeks]*3 then '2 MATs Ago'

else 'Historical'

end

Hope this helps

ZZ

1 of 1 people found this helpful
• ###### 3. Re: Choose a date and cut the data by specified number of weeks

amazing, thank you

• ###### 4. Re: Choose a date and cut the data by specified number of weeks

Thanks

Glad I could help out -

Jim