Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a test-table with one dimension:
product_id
I have the measure Min(price)
to check whether there is data or not.
I have a min price for each product_id, so do I if i select the last date by myself.
But if i try to use a set expression to ensure that the min price shows the data for the last date, i only have "-" signs in the column.
Min({ $ <filter_date = {'$(=date(floor(Max( {$<[filter_date]=>} discoverydate))))'} price)
I checked the inner
=date(floor(Max( {$<[filter_date]=>} discoverydate)))
too, and it has the intended date which i want to use as filter_date.
any ideas?
OMG...
It was a typo all the time...
the closing " >} " was missing all the time
even in your suggestions we kept missing it.
and the syntax-check don't get it in this case.
anyway i appreciate your help very much.
What is your date field format Edgar?
UPDATE: Try this
Min({$<filter_date = {'$(=Date(Floor(Max({$<[filter_date]=>} discoverydate)), 'DateFieldFormatHere'))'} price)
In my Main:
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
I load the used fields like:
...
discoveryDate as discoverydate,
Date(Floor(discoveryDate)) as filter_date,
...
I tried:
Min({ $ <filter_date = {'$(date(floor(Max( {$<[filter_date]=>} discoverydate)), 'DD.MM.YYYY'))'} price)
It doesn't work 😕
You are missing a equal sign and you need to use double quotes:
Min({$<filter_date = {"$(=Date(Floor(Max({$<[filter_date]=>} discoverydate)), 'DD.MM.YYYY'))"} price)
hm...
I "copy-pasted" your code, but still no value.
What made me wonder is that my "controll-columns"
Max( {$<[filter_date]=>} discoverydate)
and
Date(floor( Max( {1} discoverydate)))
and
min(price)
all work.
Does this, in a KPI object, give you the date you want to filter in filter_date column (and hopefully in the same format as filter_date)
=Date(Floor(Max({$<[filter_date]=>} discoverydate)), 'DD.MM.YYYY')
What is the format of discoverydate and filter_date?
I get the 16.02.2016 as KPI as expected.
discoverydate is loaded from a mysql.
I didn't change anything on purpose in the LOAD-Script.
discoverydate is presented as DD.MM.YYYY hh:mm:ss
from this discoverydate i made the filter_date with:
Date(Floor(discoveryDate)) as filter_date
filter_date is presented as DD.MM.YYYY
thus, are these the formats you are asking for, or do we have more control to define a field as date within the LOAD-Script?
So far I didn't run into trouble regarding dates.
Hardcoded:
Min( {$<filter_date = {"16.02.2016"} >} price)
works.
But in other KPIs the (bold):
count(
{ $ <calc_merchant = {"OWN_CLIENT_DATA_FLAG"},
[filter_date]= > }
distinct if(
aggr( { $ <filter_date = {'$(=date(floor(Max( {$<[filter_date]=>} discoverydate))))'} > } rank(-min(price),1,1), product_id, calc_merchant) = 1, product_id
)
)
Set Expression work.
Thus why not as measure within a Min ?