# How to have one calculated field but dynamic value

Hi ,

I have a requirement of calculating last 7 days and last 30 days performnace.

I have used below logic in filed PERIOD:

If DATE(SPLIT([SLA Date Time], " ", 1)) = TODAY()

then 'TODAY'

ELSEIF

DATE(SPLIT([SLA Date Time], " ", 1)) >= TODAY() - 7

THEN  "Last 7 Days"

ELSEIF

DATE(SPLIT([SLA Date Time], " ", 1)) >= TODAY() - 30

THEN  "Last 30 Days"

end

Problem:

If PERIOD is last 7 days , it cant be Last 30 days.

But actually If something is Last 30 days the last 7 days also should be including in there.

How do i fix this?

Thanks

• ###### 1. Re: How to have one calculated field but dynamic value

You could create a Parameter to choose the period then have a calculated field that determines if the data date is within the paremeter.

Like:

Create the Parameter:

Name: Period

Data type: String

Allowable values: List

List of values:

0      Today

7      Last 7 Days

30    Last 30 Days

Now create the Calculated Field:

Name: In Period

Calculation: DATE(SPLIT([SLA Date Time], " ", 1))<DATEADD('day',1,TODAY()) and DATE(SPLIT([SLA Date Time], " ", 1))>=DATEADD('day',-int([Period]),TODAY())

Now drag the In Period field to your filters & choose TRUE as the filter.

And Show the Period Paremeter...

1 of 1 people found this helpful

• ###### 3. Re: How to have one calculated field but dynamic value

I tried and it works fine . Thanks for that.

I now have to display it like :

1- if I am displaying today(0) then i should display number of 'Error', 'Waiting','Completed' Jobs

2-else if its 7 or 30 days I should  show only 'Error' and ' Waiting'

I have used SETS for this like :

- I created 2 sets one with E,W,and C another with E,W

- Then i wrote a calculated filed like if period='TODAY' (0r our case 0) then SET1 else SET2.

- Then i have dragged it to the pane and filtered only true.

Do you think any better way to do this?? (tried group too but , one value used in a group cant be used in another group is what i encountered).

Thanks