11 Replies Latest reply on Feb 22, 2016 1:09 AM by Jonathan Velarde

# Calculation of two different dates - Getting Number of days without displaying the actual dates

Good Day!

Hi Mam/Sir..,

Just an inquiry about date calculations.

Is it possible to get the number of days between two dates without displaying the actual dates in the worksheet?

Based on my experience my calculated field was working for the difference between two dates, but it only works if the actual date is display on the worksheet.

Here is my sample output, attached file.

The date calculation was working fine but i need to display also the dates.

What I want to my report is compute the Total Amount on the proper PD Days range.

Sample:

Nb Days is 8

Total Amount should be placed on the PD 8-14

Nb Days is 18

Total Amount should be placed on the PD 15-22

Nb Days is 0

Total Amount should be placed on the PD Current

I just need to sum the Total Amount per PD Days range and no need for me to add subtotals per store.

• ###### 1. Re: Calculation of two different dates - Getting Number of days without displaying the actual dates

hi Shawn Wallwork.., thanks for the feedback..

But upon doing this, the row still on the worksheet. The header only hides.

Regards

Jonathan B. Velarde

• ###### 2. Re: Calculation of two different dates - Getting Number of days without displaying the actual dates

Good Day!

Hi Shawn Wallwork.., thanks for this..,

For the sample workbook, we have a creadential for this. I will just create new sample workbook using a superstore data for your visualization.

Will look also on your suggestions..

Regards,

Jonathan B. Velarde

• ###### 3. Re: Calculation of two different dates - Getting Number of days without displaying the actual dates

Good Day!

Hi Shawn Wallwork..,

Please check attached file for the sample workbook.

Based on the sample worksheet, the number of days were working fine if I include the Order date on the display.

But when I remove the Order date you will see the difference between the two workbook.

I expect that same figure should display. My Subtotal was incorrect also.

Thanks in advance for the help on this.

Regards,

Jonathan B. Velarde

• ###### 4. Re: Calculation of two different dates - Getting Number of days without displaying the actual dates

Good Day!

Thanks,

Jonathan B. Velarde

• ###### 5. Re: Calculation of two different dates - Getting Number of days without displaying the actual dates

Jonathan,

Can you please post a packaged workbook. There is not source data in the workbook so we can't really see the view.

Regards

Amanjot

• ###### 6. Re: Calculation of two different dates - Getting Number of days without displaying the actual dates

Good Day!

Hi Amanjot Klair.., thanks for looking into this.,

Looking forward for much better understanding on this.

Regards,

Jonathan B. Velarde

• ###### 7. Re: Calculation of two different dates - Getting Number of days without displaying the actual dates

Good Day!

Hi Mam/Sir..,

I want to create an internal calculated field that will compute the number of days between two different dates without displaying the dates per day (Aggregated value) and compute the Total Amount per Number of days range.

Kindly check also the excel file., As I export the data from the the sample workbook I created. On the right side of the exported data I created the sample output I want to display.

Would appreciate if someone could help on this.

Regards,

Jonathan B. Velarde

• ###### 8. Re: Calculation of two different dates - Getting Number of days without displaying the actual dates

Create a cacl fields for each of the ouput field like the below. This should solve your problem.

[PD <= 60] = SUM({FIXED [Order Date] : MAX(DATEDIFF('day',[Order Date],TODAY()))})

1 of 1 people found this helpful
• ###### 9. Re: Calculation of two different dates - Getting Number of days without displaying the actual dates

Good Day!

Thanks for the feedback.,

I tried datediff for the computation of number of days DATEDIFF('day', TODAY() ,[Due Date] )*-1.  instead of aggregate using this  calculation ZN(INT(ATTR([Date])-LOOKUP(ATTR([Date]),-1)))

This is much simple to my previous calculation. I guess the previous calculation was too complicated.

Thanks and God Bless

Regards,

Jonathan B. Velarde

• ###### 10. Re: Calculation of two different dates - Getting Number of days without displaying the actual dates

Or else you may also calculate the datediff at the row level and then aggregate the max of datediff in fixed.

DateDiff = DATEDIFF('day',[Order Date],TODAY())

[<=60] = IF [DateDiff] <= 60 THEN [DateDiff] ELSE 0 END

[PD <= 60] = SUM({FIXED : MAX([<=60])}

this is a little complicated method and i am not sure if this can be nested as well... Just try this one it'll help you cagegorise as per datediff.

-Amanjot

• ###### 11. Re: Calculation of two different dates - Getting Number of days without displaying the actual dates

Good Day!

Hi Sir Amanjot Klair..,