7 Replies Latest reply on Apr 20, 2017 1:13 PM by Nathan Flanagan

LOD with If statement

Nathan Flanagan Apr 20, 2017 9:58 AM

I have tried to create this LOD calculated field.

IF ISNULL([Clear Dt])

THEN

{ FIXED [ID]: AVG([Total Cost])}

END

I would assume this to take the average of each [ID] and then if I sum the dimension when I display the field, it should return 17 thousand and something. Instead, I see that it is summing each of the values of the [total cost] without first finding the average. I don't know why this doesn't work and would love any help or suggestions.

Here is a packaged workbook as well. I hope it works, this is the first time posting a question.

Thanks, Nathan Flanagan

Example.twbx 17.6 KB

1. Re: LOD with If statement

Justin Larson Apr 20, 2017 10:04 AM (in response to Nathan Flanagan)

not so far off:

{ FIXED [ID]: avg(IF ISNULL([Clear Dt]) then [Total Cost] end ) }
2 of 2 people found this helpful
Like Show 1 Likes(1)

Actions
2. Re: LOD with If statement

Deepak Rai Apr 20, 2017 10:05 AM (in response to Nathan Flanagan)
Hi Nathan,
Your calculation is Correct. Just Change your SUM to Avg in the Text Label. Right Click>Measure>AVerage.
Thanks
Deepak

Example.twbx 17.7 KB
Like Show 0 Likes(0)

Actions
3. Re: LOD with If statement

Justin Larson Apr 20, 2017 10:34 AM (in response to Deepak Rai)

@Deepak Rai, if he does this and then pulls Id off the shelf, the result is 6648, which I do not believe is what he's after.
Like Show 0 Likes(0)

Actions
4. Re: LOD with If statement

Deepak Rai Apr 20, 2017 10:39 AM (in response to Justin Larson)

Hi Justin,
In his LOD , his intention is to FIX it to ID, So pulling off IDs from Rows doesn't come into Picture. That would be a different scenario.
Thanks
Deepak

{ FIXED [ID]
Like Show 0 Likes(0)

Actions
5. Re: LOD with If statement

Nathan Flanagan Apr 20, 2017 10:44 AM (in response to Deepak Rai)

Thanks for the help!
Okay, I don't think I explained my self well enough.
So, we have more one with multiple id's with on price. And we have another Id with a different price. The price will repeat for the same id. There fore I want to find the average of each id. Then I want to find the sum of those.
Since there is one Id with an average of 11 thousand. And an Id with 8 rows of 6 thousand each. I want it to return one one row of 6 thousand and one row of 11. So when I sum it up it will equal 11 thousand.
Does that make sense?

--
Nathan Flanagan
Like Show 0 Likes(0)

Actions
6. Re: LOD with If statement

Justin Larson Apr 20, 2017 10:52 AM (in response to Nathan Flanagan)

That's how I understood your original post. See the alteration in my original reply. Should do the trick.
Like Show 0 Likes(0)

Actions
7. Re: LOD with If statement

Nathan Flanagan Apr 20, 2017 1:13 PM (in response to Justin Larson)

Justin,
That worked perfectly!

Thanks so much,
-Nathan Flanagan
Like Show 0 Likes(0)

Actions