8 Replies Latest reply on Aug 23, 2017 5:20 AM by Lucas Gros

# Double aggregation - CNTD of products with stock above minimum

Hi,

It looks like my need needs a double aggregation:

I analyse product monthly sales.

Products have an initial stock.

Products belong to a Style category

I calculate the "running stock": initial stock - running_sum(sum(qty sold))

Then, for each category, I want to calculate the monthly CNTD of products that have a stock above minimum.

That's where the problem is: calculated fields or LODs refuses to do double aggregation. So I cannot see the totals per category:

Anyone knows how to do that?

Thank you and have a great day.

Best

Lucas

• ###### 1. Re: Double aggregation - CNTD of products with stock above minimum

Hi - I'm a little confused with your terminology - different between the narrative and the workbook but if your products = reference then see below and attached

countd  =   if [Running Sales]>0 then countd([Reference]) end

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Double aggregation - CNTD of products with stock above minimum

Hi, Lucas

Below is the screen shot of steps to achieve this.

Hope this could help

ZZ

1 of 1 people found this helpful
• ###### 3. Re: Double aggregation - CNTD of products with stock above minimum

Hi Jim,

thanks for the help. Did you attach a workbook? I don't manage to replicate your results just with the formula proposed.

Sorry for the mismatch between narrative and workbook. You're absolutely right, so here is the narrative corrected:

I analyse monthly sales of Reference(s).

Each Reference has an initial stock.

Reference(s) belong to Categories

I calculate the Running Stock = ATTR([Initial Stock])-[Running Sales]

Running Sales being the RUNNING_SUM(SUM([QTY]))

Then, for each Category, I want to calculate the monthly CNTD of References that have a stock above Minimum stock to count as Offer.

That's where the problem is: calculated fields or LODs refuses to do double aggregation. So I cannot see the totals per Category.

• ###### 4. Re: Double aggregation - CNTD of products with stock above minimum

sorry see attached

Jim

• ###### 5. Re: Double aggregation - CNTD of products with stock above minimum

Hi Zhouyi,

thanks for the help.

I identified a bug and wonder how to calculate the % total:

1.There seems to be an bug: displayed values are not correct (Minimum stock to count as Offer threshold seems not to be respected) - see screenshot below.

2. The final step is to calculate the Percent of Total. So here it would be like:

1st column: 33% and 66%

2nd column: 50%, 25% and 25%

3rd column: 100%

Thanks

Lucas@

• ###### 6. Re: Double aggregation - CNTD of products with stock above minimum

Jim,

The solution you propose works on the Sales. Instead, what I want to show is the CNTD(References that have a stock >= Minimum stock to count as offer).

As you can see in screenshot below, your solution is taking into account References that have less stock than the minimum authorized.

I wanted to insert the workbook updated with yours and Zhouyi's proposed solutions but doesn't seem to be possible in this reply...

• ###### 7. Re: Double aggregation - CNTD of products with stock above minimum

Hi, Lucas

I tried to understand what you want to achieve and update my solution based on my understanding. I highlighted the calculation fields I added or modified below

I attached the workbook for your reference.

Here is a screen shot of end result I have.

Hope this could help

ZZ

1 of 1 people found this helpful
• ###### 8. Re: Double aggregation - CNTD of products with stock above minimum

Thanks a lot Zhouyi, that does exactly what I wanted

I still have challenges on top of this but that's another topic... Thanks again!

Just in case you have an idea about it: do you know how I can calculate the difference between the two graphs?