Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

Nested 'measure' in Set expression

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?

1 Solution

Accepted Solutions

Re: Nested 'measure' in Set expression

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)


talk is cheap, supply exceeds demand
7 Replies

Re: Nested 'measure' in Set expression

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)


talk is cheap, supply exceeds demand
Not applicable

Re: Nested 'measure' in Set expression

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.


Not applicable

Re: Nested 'measure' in Set expression

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

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.


talk is cheap, supply exceeds demand
Not applicable

Re: Nested 'measure' in Set expression

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

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


talk is cheap, supply exceeds demand
Not applicable

Re: Nested 'measure' in Set expression

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.