7 Replies Latest reply on Dec 25, 2018 6:11 AM by Norbert Maijoor

# nested LOD calculation of sum across category

Category Id
Subcategory Id
Qty
id1subid15
id1subid15
id1subid22
id1subid22
id1subid31

I have a dataset that has category id, subcategory id and qty . There are others columns resulting in repetition of the same subcategory.

I want to use and IF statement to filter out some Category Id and get the sum. I have to do it in two fold

FIRST USE :

IF [Category id] = 2834316 OR [Category id] = 2836481 THEN

{ EXCLUDE [Subcategory Id] :

SUM({ INCLUDE [Subcategory Id],[Category id]:MAX([Qty])})}

ELSE

0

END

Then create a sum field :

SUM(

{ FIXED [Category id] : MAX([qty by category]) }

)

Is there a method of achieving this without using a sum field.

• ###### 1. Re: nested LOD calculation of sum across category

Hi Nellie,

Not sure but find my approach as reference below and stored in attached workbook version 10.5 located in the original thread.

M1. max Qty: if attr([Category id])!=2836481 then max({fixed [Category id],[Subcategory Id]:max([Qty])}) end

Hope it helps.

Regards,

Norbert

• ###### 2. Re: nested LOD calculation of sum across category

@Thanks Norbert.  This works if I have Category Id in the shelf. I used the formula

IF attr([Category id])=2834316 or attr([Category id])=2836481

then

sum({fixed [Category id],[Subcategory Id]:max([Qty])} )

end

The moment I want the the sum of 52 + 34, this does not work. Let me know if you have a solution.@Nobert Maijoor

1 of 1 people found this helpful
• ###### 3. Re: nested LOD calculation of sum across category

Hi Nellie,

Upfront! Merry Christmas:)

Regards,

Norbert

• ###### 4. Re: nested LOD calculation of sum across category

Hello Nellie,

Please check if you want result as below:

1) Write calculation as

2) write another calculation as below

3) result will be like this

I/P :

o/p:

• ###### 5. Re: nested LOD calculation of sum across category

Thanks Norbert. Merry Christmas !

This solution worked

• ###### 6. Re: nested LOD calculation of sum across category

Thanks Norbert. Merry Christmas !

This solution worked

• ###### 7. Re: nested LOD calculation of sum across category

Hi Nellie,

You are welcome:) Please close the thread with a "helpful" or "Correct Answer" so the community is aware and can focus on other challenges.