Yesterday in set analysis

[pascaldijkshoor]

Hello again

In a formula, i want to sum up some measures from the day before the date that is displayed as a dimension. I am using the following formula to calculate the current date. Also the date field is called: Activity_Date

sum(frac(EXTRA_TIME_NEXT_DATE))

I have already tried many formulas that are on this forum. Also i tried setting a variable for yesterday and use that variable in the set analysis. However i did not get it to work yet.

[Digvijay_Singh]

Do you want to compare Activity_Date with EXTRA_TIME_NEXT_DATE? Not sure what exactly you are trying? Can you share the formula you tried and didn't work?

[Digvijay_Singh]

Yesterday is simply DateField-1 if your datefield is formatted as date type. But in calculations the field needs to be surrounded by aggregated functions like Max(Date)-1 else it calculates into null due to more than one value possible.

[pascaldijkshoor]

No the activity date is one of my dimensions. The exta_time_next_date is a calculated time field.

for example I have on ACTIVITY_DATE = 28 March three EXTRA_TIME_NEXT_DATE fields that are:

- 05:15 hours

- 06:30 hours

- 01:30 hours

Total = 13:15 hours

For example i want a bar chart with ACTIVITY_DATE as a dimension, where i calculate for each ACTIVITY_DATE the total EXTRA_TIME_NEXT_DATE of the day before the displayed date.

Eventually i want to combine this formula with a formula that sums up another calculated field on the current date, so i cannot make changes in the dimensions.

[pascaldijkshoor]

Up