7 Replies Latest reply on May 7, 2018 12:27 AM by David Maning

# Use max function in Date logic

Hi Team,

Here i came with a problems which seems to look easy but i did not track this logic.

My requirement would complete with below steps

1. Find the sales on max date

2. Find sales on max date -1

3. Subtract step 1 - step 2

3. if day of date =1 then step 1 else step 3.

You can use sample super store data for this.

Regards,

Aman

• ###### 1. Re: Use max function in Date logic

Hi Singh,

Please find my solution attached.

Kind regards,
D

• ###### 2. Re: Use max function in Date logic

Hi David,

Yahh right now value is coming correct but can you validate your logic.

When max date will become 1 then if statement will execute.

I told you this because i did this same calculation but this logic failed on 1st day of month.

Regards,

Aman

• ###### 3. Re: Use max function in Date logic

Good morning

please see the attached approach using LOD's

first  the max date =

then the sales on the 2 days are

and the difference is

it returns this

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.

1 of 1 people found this helpful
• ###### 4. Re: Use max function in Date logic

Hi,

Based on you post the max date is the max date in data set. So it's a constant figure. In could be become changeable via the parameter or window calculations. So if the day of max date would be 1 then the logic point 1 will apply accordingly.

Trust this helps.

D

• ###### 5. Re: Use max function in Date logic

Hi Aman,

You can easily test whether a given solution is correct for the day 1 logic by replacing Max Date with any first day of the month. For example, open all 3 calculated fields from David and replace {MAX([Order Date])} with #01/12/2017#. If you do, then you will discover that his solution will work on the first day of the month. I just did the testing for you and below are the results to confirm that.

Hope this helps.

Ossai

2 of 2 people found this helpful
• ###### 6. Re: Use max function in Date logic

Hi David,

Thanks for your solution.

But I have some doubt. The logic which i used is given below -

Find max date sales -

sum( IF [Order Date]={ FIXED :MAX([Order Date])}

THEN

[Sales]

END)

Find max date -1 sales -

sum( IF [Order Date]={ FIXED :MAX([Order Date])-1}

THEN

[Sales]

END)

Final -

IF ATTR( DATEPART('day',[Order Date]))=1 THEN

[max date sales]

ELSE

[max date sales] - [max date -1 sales]

END

But it did not work. Can you confirm on this?

Regards,

Aman

• ###### 7. Re: Use max function in Date logic

Hi Singh,

First, can you please clarify your doubts?

Regarding your calculations, I suggest to amend final step. There is specification of which date should be equal to one. This should be the last available date, i.e. the maximum available date.

Trust this helps.

D