Nested 'measure' in Set expression

[#1]

Hi,

I have a table with merchants as Dimension.

This table should show data for one specific product_id.

I managed this by using a variable.

As 2nd dimension i use:

Only(aggr( Max( {$ <product_id = {'$(varDetailProductId)'}, filter_date = >} discoverydate), offer_merchant))

which basically is the latest date where we have a price information for that merchant.

so i end up with only one row per merchant.

now i want more columns:

* the price on that latest date

* the price on the date before

I tried:

min( {$ <product_id = {'$(varDetailProductId)'}, filter_date = {'$(aggr( Max( {$ <product_id = {$'(varDetailProductId)'}, filter_date = >} discoverydate), offer_merchant)))'} >} price)

but as i have two nested measures (the aggr and the inner var) the ' is not escaped.

but even if i could escape that, i am not sure it will work. 😕

bottom line:

if I use

{ $ <some_field = {'$(myExp( other_fields))'} >}

what i called "nested" is it possible to nest this whole expression agin inside another set expression?

[Gysbert_Wassenaar · Accepted solution]

That's rather convoluted. Why such a complicated calculated dimension?

Using an expression instead of a calculated dimension looks much easier to me:

     Max( {$ <product_id = {'$(varDetailProductId)'}, filter_date = >} discoverydate)

The other columns could be

     FirstSortedValue( {$ <product_id = {'$(varDetailProductId)'}, filter_date = >} price ,-discoverydate)

     FirstSortedValue( {$ <product_id = {'$(varDetailProductId)'}, filter_date = >} price ,-discoverydate, 2)

[#3]

I started using it like you mentioned it, but it told me that this is a invalid dimension.

If I instead use this as measure, I have all the merchants in my list even those who have no offer for the specified product.

how can I add the Set expression:

{$ <product_id = {'$(varDetailProductId)'}, filter_date = >}

to the merchant dimension field?0

or as more generall question:

Can i use a set expression for a whole table (later hypercube)?

Such as:

Show me the dimensions and measures but do this on data where { $ ... } is valid.

[#4]

btw: the prices works great so far

THX,

now I need to check, how to reduce the merchants to the given product only.

[Gysbert_Wassenaar]

I started using it like you mentioned it, but it told me that this is a invalid dimension.

That's because it is, as I said, not a calculated dimension, but an expression.

[#6]

Ok, I understand that now.

Do you have any idea to my other problem?

How can I reduce the one used dimension with something like a set expression?

[Gysbert_Wassenaar]

What problem is that? I think I already posted an expression you should use instead of a calculated dimension.

[#8]

I thought I could not put any condition to a dimension.

Thus i searched a way to reduce the dimensions to the rows I want to show.

I now work found how to deal with that.

Like:

if (product_id = $(varDetailProductId), product_id)

as dimension.

I think I will be able to build my table now.

Thx for ur help.