6 Replies Latest reply on Oct 2, 2018 1:45 AM by Haan Haan

# Return Value for Min & Max Date in a selected period

Kindly refer to the attached workbook.

What are the formula that will return when i select

YTD-  (26 Sept 2018- 1 Jan 2018) which is   1487- 1482 = 5

QTD- (26 Sept 2018- 1 July 2018)   1487-640=847

MTD- (26 Sept 2018- 1 Sept 2018)  1487-1262=225

It is actually the formula that will return max date of sales minus min date of sales based on the period selected.

Thanks

• ###### 1. Re: Return Value for Min & Max Date in a selected period

Haan:

YTD:

SUM(IF [Order Date]=today() THEN [Sales] END)

-

SUM(IF [Order Date]=DATETRUNC('year',today()) THEN [Sales] END)

QTD:

SUM(IF [Order Date]=today() THEN [Sales] END)

-

SUM(IF [Order Date]=DATETRUNC('quarter',today()) THEN [Sales] END)

MTD:

SUM(IF [Order Date]=today() THEN [Sales] END)

-

SUM(IF [Order Date]=DATETRUNC('month',today()) THEN [Sales] END)

Hope it helps.

Michael Ye

• ###### 2. Re: Return Value for Min & Max Date in a selected period

Thanks Micheal.

However, i have a parameter on the period.

May I know how to configure the formula to enable the user choose the YTD, MTD, QTD and display the answer accordingly?

Furthermore, sales today should minus the first day of the year if the selection is YTD.

As the sales is aggregated value, so need to get the number on that day to carry out the calculation.

Hope you get me.

• ###### 3. Re: Return Value for Min & Max Date in a selected period

Haan,

Please see the workbook and screen shot. Build calculated field:

SUM(IF [Order Date]=today() THEN [Sales] END)

-

SUM(IF [Order Date]=DATETRUNC([Select Period],today()) THEN [Sales] END)

Please note ,DATETRUNC(['year'],today()) means: 1/1/2018

DATETRUNC(['quarter'],today()) means: 7/1/2018

DATETRUNC(['month'],today()) means: 9/1/2018.

Use parameter to name the Title.

Hope it helps.

Michael Ye

1 of 1 people found this helpful
• ###### 4. Re: Return Value for Min & Max Date in a selected period

Thanks Micheal

However, why i looked into the workbook, when YTD, MTD and QTD is chosen, the answer is different with the screenshot by you?

• ###### 5. Re: Return Value for Min & Max Date in a selected period

Haan,

Yesterday when I answer your question, the date is 9/30/2018, today() means that day. The sale is \$541.

From the record,

1/1/2018: \$1,482

7/1/2018: \$640

9/1/2018: \$1,262

So the YTD: 541-1482=-\$941

QTD: 541-640=-\$99

MTD: 541-1262=-\$721.

They are all correct.

When you open today, the date (today()) changes, which is 10/1/2018. The sale also changes.

Michael Ye

• ###### 6. Re: Return Value for Min & Max Date in a selected period

Thanks Micheal.