1 Reply Latest reply: Oct 3, 2016 6:43 AM by Antonio Mancini

# 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?

en QlikView Creating Analytics Compartir Responder

Mostrar menos

• ###### Re: Set Analysis- Take into account Total Dates

Hi Beatriz,

Dimension : Product,Store

Expression :

If(Dimensionality()=1,
Count(DISTINCT Product)&'/'&Sum(Aggr(If(Max(AVAILABILITY) >=1,Count(Date)),Product,Store)),
Sum(Aggr(If(Max(AVAILABILITY) >=1,Count(Date)),Product,Store
)))

Regards,

Antonio