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

    SET ANALYSIS- TAKE INTO ACCOUNT TOTAL DATES

    Beatriz Manjón

      Hello all,

       

      I would like to expose a problem I am experiencing when obtaining a correct value.

       

      The example is:

       

       

      PRODUCT X
      STOREDATE PRODUCT X AVAILABILITY
      Store ADate 11
      StoreADate 20
      Store BDate 11
      Store BDate 20
      Store CDate 11
      Store CDate 21
      Store DDate 10
      Store DDate 20

       

       

      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