Hello all,
I would like to expose a problem I am experiencing when obtaining a correct value.
The example is:
| PRODUCT X |
| STORE | DATE | PRODUCT X AVAILABILITY |
| Store A | Date 1 | 1 |
| StoreA | Date 2 | 0 |
| Store B | Date 1 | 1 |
| Store B | Date 2 | 0 |
| Store C | Date 1 | 1 |
| Store C | Date 2 | 1 |
| Store D | Date 1 | 0 |
| Store D | Date 2 | 0 |
I would like to have an Average of SKU as follows: 1/ 6. ( 6 refers to the total store-dates where the product is available ( Store A-Date 1-Date2, Store B-Date 1-Date2, Store C-Date 1-Date2, )
The numerator is clear for me, but when calculating the denominator I am facing problems since it only takes into account the dates and stores where the product is available and not that store-date when the product is not available.
Currently, I am using the following formula in order to get the denominator:
sum(TOTAL <id_tienda, Date> aggr(Count (TOTAL <Store,Date> {< $(=concat(DISTINCT {< $Table={'Products'} >} Chr(39) & $Field & Chr(39), ','))>} DISTINCT Store), Store,Date))
Could someone help me?
Thanks in advance
