0 Replies Latest reply: Oct 3, 2016 5:41 AM by Beatriz Manjón

# SET ANALYSIS- TAKE INTO ACCOUNT TOTAL DATES

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?