topic Nested 'measure' in Set expression in App Development

Nested 'measure' in Set expression

Mon, 21 Mar 2016 14:22:54 GMT

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?

Re: Nested 'measure' in Set expression

Gysbert_Wassenaar — Mon, 21 Mar 2016 14:35:50 GMT

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)

Re: Nested 'measure' in Set expression

Mon, 21 Mar 2016 14:47:49 GMT

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.

Re: Nested 'measure' in Set expression

Mon, 21 Mar 2016 15:00:41 GMT

btw: the prices works great so far

THX,

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

Re: Nested 'measure' in Set expression

Gysbert_Wassenaar — Mon, 21 Mar 2016 16:31:37 GMT

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.

Re: Nested 'measure' in Set expression

Tue, 22 Mar 2016 08:44:21 GMT

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?

Re: Nested 'measure' in Set expression

Gysbert_Wassenaar — Tue, 22 Mar 2016 11:45:05 GMT

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

Re: Nested 'measure' in Set expression

Tue, 22 Mar 2016 11:49:43 GMT

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.