Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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)
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)
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.
btw: the prices works great so far
THX,
now I need to check, how to reduce the merchants to the given product only.
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.
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?
What problem is that? I think I already posted an expression you should use instead of a calculated dimension.
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.