How to calculate distinct values ?

Hi All,

I have a list which has Product as parent and SKU as list. I am trying to calculate the number of SKUs with demand > 0 for all periods. Right now, using this formula - If demand > 1 then 1 else 0. The issue with this formula is that the summary adds individual SKUs with demand at yearly level. See below screenshot -

Product 1 has only one sku which has demand in all the years.

	F21	F22	F23	F24	F25	All Periods
Product 1	1	1	1	1	1	5

Ideally, all periods should give only 1 as the answer as there is only one unique sku under product 1 having demand. Any leads will be appreciated.

Thanks in advance.

Accepted answers

All comments

AntonMineev

Hi @Akshay07! Your formula is correct. If you want it not to sum to ALL PERIODS, change the Summary method of LI to "Formula" (for time). Then the calculation should be correct.

Снимок_экрана_2022-04-27_в_08_136563.png

Akshay07

Hi @AntonMineev - Thank you for your response. I tried using the formula for summary. The problem is, the module is dimensioned by List A (Product as parent and SKU as list) and the line item (to calculate skus with demand) is being applied applied to List B (Only has product and no child). Hence a subsidiary view is created, in this view the summary is getting calculated correctly but in the main module it does not. Is there any other solution to calculate ?

AntonMineev

Can you attach a small diagram or blueprint? It's just my understanding that if you calculate correctly on the parts, you can then pull to a less detailed LI (you may also need to check the summation method).

Of alternative ideas, use the SUM formula and technical mapping to shift 1 to the right analytics.

alexpavel

@Akshay07 Not sure exactly what is your final goal, but in order to find what SKU's have demand, you could create a subsidiary line-item without the Time list and have the formula like: If timesum(demand) > 0 then 1 else 0

Hope it helps

Alex

Quick Links

Can you help?

Unanswered questions

Best way to Architect Financial Statements in Anaplan for Scalability
I have seen two methods used to build financial statements and I am wondering what others have experienced in scalability/maintenance between the two or if there is a better approach. Assume that we have a flat data source for our general ledger within Anaplan. Our line item we are focused on within the source data is the…
Curious Comparison: Forecast Modeling for Unusual Products
I've been experimenting with some forecast modeling techniques in Anaplan recently and decided to test them on a few quirky, offbeat product categories just for fun. One of the categories I played around with was love dolls—yes, you read that right. It actually turned out to be more interesting than expected. Unlike…
OData Rest API for
Hi, Does anyone know how to get an outbound OData Rest API, (OAuth2) to establish a connection between SAP and Anaplan. Thanks Khaled