5 Replies Latest reply on Mar 13, 2017 3:28 PM by Mike Janecek

# Using LODs in an if statement to get values for 1 day/7days prior

Using V10.1

Hello,

I am trying to get values across today, 1 day prior, and 7 days prior.  I cannot change the input data format.

Values I am getting:

The today and 1/7 day prior are giving me the same result.

1/7 day prior should not be the same value as today. However 1/7 day prior should be somewhere in the realm of the today value.

These are the calcs I'm using:

What I need to happen: 1/7 day prior should not be the same value as today. However 1/7 day prior should be somewhere in the realm of the today value shown above.

Thanks in advance for any help.  I've attached a wkbk.

Mike J

• ###### 1. Re: Using LODs in an if statement to get values for 1 day/7days prior

Looks like you are really close

I think you are going to need a nested level of detail calculation

something like { FIXED  : Avg(if { FIXED : Max([Start Date])-1} == [Start Date] then [ALC(seconds)] end)}

Check out Filter to max date - FIXED not working? for more in depth discussion of nested LODs and dates

Hope this helps!

• ###### 2. Re: Using LODs in an if statement to get values for 1 day/7days prior

Thanks Jay,

Using the calc you are showing yields the bottom two results below:

Results I need are:

I think I need the SUM calc below  ... the [ALC(seconds)] is not using the SUM fields

I get the aggregate error if I try to insert it in the nested level of detail.

Included an update wkbk.

Thanks again

• ###### 3. Re: Using LODs in an if statement to get values for 1 day/7days prior

MAX(if { FIXED : Max([Start Date])-1} == [Start Date] then {fixed [Start Date]: [Today(seconds) W SUM]} end)

or

{Fixed : MAX(if { FIXED : Max([Start Date])-1} == [Start Date] then {fixed [Start Date]: [Today(seconds) W SUM]} end)}

depending on if you are planing to filter by start date, heres the difference:

• ###### 4. Re: Using LODs in an if statement to get values for 1 day/7days prior

Sorry it took so long to get back to you, Jay, but thank you for the help.  My first leap to a nested LOD.