How to calculate distinct values ?
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.
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.
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.1
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 ?0
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.0