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

nested LOD calculation of sum across category

Nellie Rajarova Dec 24, 2018 1:44 AM

Category Id	Subcategory Id	Qty
id1	subid1	5
id1	subid1	5
id1	subid2	2
id1	subid2	2
id1	subid3	1

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

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.

LOD sum.twbx 39.6 KB

1. Re: nested LOD calculation of sum across category

Norbert Maijoor Dec 24, 2018 8:03 AM (in response to Nellie Rajarova)
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

LOD nalmai_v10.5.twbx 65.6 KB
Like Show 0 Likes(0)

Actions
2. Re: nested LOD calculation of sum across category

Nellie Rajarova Dec 24, 2018 8:53 PM (in response to Norbert Maijoor)

@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
Like Show 0 Likes(0)

Actions
3. Re: nested LOD calculation of sum across category

Norbert Maijoor Dec 25, 2018 2:28 AM (in response to Nellie Rajarova)
Hi Nellie,

Upfront! Merry Christmas:)

Find attached my adjusted approach

Regards,
Norbert

LOD nalmai updated_v10.5 (1)_v10.5.twbx 95.6 KB
Like Show 0 Likes(0)

Actions
4. Re: nested LOD calculation of sum across category

megha gawali Dec 25, 2018 2:42 AM (in response to Nellie Rajarova)

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:
Like Show 0 Likes(0)

Actions
5. Re: nested LOD calculation of sum across category

Nellie Rajarova Dec 25, 2018 5:35 AM (in response to Nellie Rajarova)

Thanks Norbert. Merry Christmas !
This solution worked
Like Show 0 Likes(0)

Actions
6. Re: nested LOD calculation of sum across category

Nellie Rajarova Dec 25, 2018 5:37 AM (in response to Norbert Maijoor)

Thanks Norbert. Merry Christmas !
This solution worked
Like Show 0 Likes(0)

Actions
7. Re: nested LOD calculation of sum across category

Norbert Maijoor Dec 25, 2018 6:11 AM (in response to Nellie Rajarova)

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.
Your cooperation is appreciated.

Have a nice evening.

Regards,
Norbert
Like Show 0 Likes(0)

Actions