Dynamic week formula

Hi Lars Tintelnot

When you say "The logic should also respect month and year change" what do you mean by that? That the results should only include dates WITHIN a particular month / year?

Please elaborate.

Regards,

Ian

Hi Lars Tintelnot

In the absence of a reply to my question, I'll assume that the days to add up can cross month boundaries.

We need to identify the day of the week form your Date Field. We can do that with the following PQL DateFormat() function. The DateFormat() function requires a DateTime as input so we need to convert your Date to a DateTime. We want to do this for every Date in the rows, so we use the .currentmember() function to do that and also return the Date as a string to the DateTime function, so we add the .caption() function as well. The date field in my model is called [Data].[Shipping Date], obviously replace with your date field

DateFormat(StringtoDateTime([Data].[Shipping Date].currentmember.caption), "EEEE")

The mask "EEEE" will return the day of the week in full.

We then need to test each Date and add up the days preceding that date, 3 days if it is a Monday, two days if it is a Wednesday or Friday. We can use the CASE statement to list the conditions and calculations. 

CASE(

    DateFormat(StringtoDateTime([Data].[Shipping Date].CurrentMember.caption), "EEEE") = "Monday",
        ([Data].[Shipping Date].LAG(1),[measures].[Data Sales])+
        ([Data].[Shipping Date].LAG(2),[measures].[Data Sales])+
        ([Data].[Shipping Date].LAG(3),[measures].[Data Sales]),

    DateFormat(StringtoDateTime([Data].[Shipping Date].CurrentMember.caption), "EEEE") = "Wednesday",
        ([Data].[Shipping Date].LAG(1),[measures].[Data Sales])+
        ([Data].[Shipping Date].LAG(2),[measures].[Data Sales]),

    DateFormat(StringtoDateTime([Data].[Shipping Date].CurrentMember.caption), "EEEE") = "Friday",
        ([Data].[Shipping Date].LAG(1),[measures].[Data Sales])+
        ([Data].[Shipping Date].LAG(2),[measures].[Data Sales])
)

The LAG() function will return the Date 1, 2 or 3 days before the Weekday in question. We could use a RANGE function instead of explicitly using each LAG() value, like

SUM(RANGE([Data].[Shipping Date].LAG(1),[Data].[Shipping Date].LAG(3)),[measures].[Data Sales])

and I would if there was a wider range of values to sum.

Save the calculation as a Measure and add to your Discover. In this case I've also created a Measure with the day of the week in it so the weekday is more obvious.

Hope that helps.

Ian