3 Replies Latest reply on Sep 25, 2018 12:43 AM by Andreas Kampianakis

# Discrete LOD on Hierarchy

Hello world,
I would like to create a sheet that gives me the the difference between the sales of the first time and the sales of the last time. In order to do that I did a calculation like this.

{fixed [Category],[Sub-Category],[Product Name]: sum(IF [Order Date] = {fixed [Category],[Sub-Category],[Product Name]: max([Order Date]) } then [Sales] end) }-{fixed [Category],[Sub-Category],[Product Name]:

sum(IF [Order Date] = {fixed [Category],[Sub-Category],[Product Name]: min([Order Date]) } then [Sales] end) }

But when I put it in a hierarchy the whole thing is getting blown up.!
So please, could someone help over this issue?
I'd like this sales difference to be shown correctly no matter if I see in the hierarchy the category or I have expanded it til the product name.
I also include a .twbx.
Thanks everyone.!

• ###### 1. Re: Discrete LOD on Hierarchy

Hi,

Is that what you're looking for?

Workbook attached for your reference. Let us know if this help.

Mahfooj

• ###### 2. Re: Discrete LOD on Hierarchy

Well, this is different from the application I want in plenty of levels, you can check the twbx file I've uploaded.
As time is on the columns and I want only to take the difference between the first and the last sales, I've put this in the discrete level, else I guess I'm gonna see that number plenty of times.!

• ###### 3. Re: Discrete LOD on Hierarchy

Also I thought that a good way to address this issue was to use the total, with a twist:

if size()>1

then

[Sales]

ELSE

[Inventory Drop]

end

where Inventory Drop is the formula of difference between first and last sale, but it doesn't work when you have null dates between data I think