Best before by...

[cristian_av]

Hi

I need to separate product based on their expiration date. I have two fields called:

- "Max Time":  Products dimension, has the number of days that a product is still in good quality

- "Item Time":  Has the number of days since the product has been manufactured, from today.

I want to create an expression with set expression, to count all the products that are out-dated. I was trying with something like this, but is not working:

=Sum({<ItemTime={">$(=MaxTime)"}>}#Box)

How should I do this?

Thanks!!

[cristian_av]

hehe Yes, I realized that. But still not working.. with the expression that gave me Manish

=Sum({<ItemTime={"=ItemTime >= MaxTime"}>}#Box)

I get the correct values, but only when I select the dimension Product.

With the expression you gave me, I only get zero values.

=Sum({<Product = {"=ItemTime > MaxTime"}>} #Box)

I've replaced for the real dimension names, with no luck. No idea why!

Thanks

[sunny_talwar]

Will a single Product always have a single ItemTime and MaxTime or a product can have more than one value for a single Product?

[agigliotti]

let's try using:

=Sum( Aggr( Sum( {< Product = {"=ItemTime > MaxTime"}>} #Box), Product ) )

[cristian_av]

A single product can have more than one Item time, because it depens on the id of each box.

I'm preparing a qvw with example data!

[sunny_talwar · Accepted solution]

May be you need this then

=Sum({<BoxIDField = {"=ItemTime > MaxTime"}>} #Box)

[cristian_av]

Here is an example with data. Hope somebody can help me!

Thanks!!

[cristian_av]

Didn't work! I've just uploaded example data. Thanks!

[MK_QSL]

have you tried this?

=Sum({<ProductKey={"=BoxTime <= MaxTime"}>}#Box)

[MK_QSL]

Or create another productkey

Product&ProductGroup&#Box as ProductKey2,

and use

=Sum({<ProductKey2={"=BoxTime <= MaxTime"}>}#Box)

[sunny_talwar]

Are you sure it gives you the right values after you make the product selection? Is that something you have tested and are 100% positive about? Not that I think it is wrong, but just want to make sure