6 Replies Latest reply on Jun 16, 2018 5:16 PM by Jalal Hassan

# DATERUNC Calculations & Aggregations

Hi,

So I'm attempting to do a DATERUNC calculation, and for the life of me I cannot get it to pull the correct data with aggregations.

(I have a secondary data source that I need location data from to mix with my financial data)

The Two Calculations are below,

Prior Period

--------

```IF
DATETRUNC([Period Length],([Book Date]))=
DATETRUNC([Period Length],DATEADD([Period Length],-
IIF([Period Comparison]=1,1,
IIF([Period Comparison]=2 AND [Period Length]="day",365,
IIF([Period Comparison]=2 AND [Period Length]="week",52,
IIF([Period Comparison]=2 AND [Period Length]="month",12,
IIF([Period Comparison]=2 AND [Period Length]="quarter",4,
IIF([Period Comparison]=2 AND [Period Length]="year",1,0)))))),[Period End Day]))
AND
DATETRUNC('day',([Book Date]))<=
DATETRUNC('day',DATEADD([Period Length],-
IIF([Period Comparison]=1,1,
IIF([Period Comparison]=2 AND [Period Length]="day",365,
IIF([Period Comparison]=2 AND [Period Length]="week",52,
IIF([Period Comparison]=2 AND [Period Length]="month",12,
IIF([Period Comparison]=2 AND [Period Length]="quarter",4,
IIF([Period Comparison]=2 AND [Period Length]="year",1,0)))))),[Period End Day]))

THEN

SUM([Backoffice Properties].[Properties ])

END
```

Current Period

--------

```IF
(DATETRUNC([Period Length], ([Book Date])) =
DATETRUNC([Period Length], [Period End Day])
AND
DATETRUNC('day', ([Book Date])) <=
DATETRUNC('day', [Period End Day]))

THEN

SUM([Backoffice Properties].[Properties ])

END
```

I'm not sure how to keep the purpose of the calculation and also get it to return the correct values.

Current I have been trying to attempt a MIN/MAX of the (Book Date), but it turns into just returning the same values for both periods.

Any help would be appreciated.

Regards,

Jalal Hassan

• ###### 1. Re: DATERUNC Calculations & Aggregations

HI Jalal

Could you please attach your sample data as twbx format.

We don't need actual data, but want mocked up.

Thanks,

Shin

• ###### 2. Re: DATERUNC Calculations & Aggregations

Hello Jalal,

Without having a look at the workbook and the data, it's fairly difficult to give detailed advices.

So please attach a sample workbook.

Besides, from your descriptions I suppose,

• You have multiple data sources and data blend is used.
• Your calculated fields have no errors, but don't give correct results.

There's a possibility that

• The relationship settings between the data sources are not correct.
• Linking field settings are not correct.

Regards

Lei

• ###### 3. Re: DATERUNC Calculations & Aggregations

Also make sure that your date field is date only and not date/time.  Even with the DATETRUC the time stamp stays and wont match with unlike field types.

G

• ###### 4. Re: DATERUNC Calculations & Aggregations

Shinichiro,

Apologies, I attempted using Sample data included in Tableau but it does not have the same dimensions as my data, and thus wouldn't be an accurate test case. I'd upload my twbx but I am working on a financial project and can't have the information visible. I'll attempt to build a sample data set of older data that I can upload and share.

Regards,

Jalal Hassan

• ###### 5. Re: DATERUNC Calculations & Aggregations

Garth,

Yes, my date field is date only. No timestamp.

• ###### 6. Re: DATERUNC Calculations & Aggregations

I have come up with a solution. I'll post it tomorrow.

Thanks for all the help.

~Jalal