topic Set expression in Min() leads to '-' in App Development

Set expression in Min() leads to '-'

Wed, 16 Mar 2016 12:08:30 GMT

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?

Re: Set expression in Min() leads to '-'

sunny_talwar — Wed, 16 Mar 2016 20:17:04 GMT

What is your date field format Edgar?

UPDATE: Try this

Min({$<filter_date = {'$(=Date(Floor(Max({$<[filter_date]=>} discoverydate)), 'DateFieldFormatHere'))'} price)

Re: Set expression in Min() leads to '-'

Thu, 17 Mar 2016 10:49:10 GMT

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 😕

Re: Set expression in Min() leads to '-'

sunny_talwar — Thu, 17 Mar 2016 11:03:11 GMT

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)

Re: Set expression in Min() leads to '-'

Thu, 17 Mar 2016 11:11:32 GMT

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.

Re: Set expression in Min() leads to '-'

sunny_talwar — Thu, 17 Mar 2016 14:30:15 GMT

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')

Re: Set expression in Min() leads to '-'

MK_QSL — Thu, 17 Mar 2016 14:32:48 GMT

What is the format of discoverydate and filter_date?

Re: Set expression in Min() leads to '-'

Thu, 17 Mar 2016 15:17:56 GMT

I get the 16.02.2016 as KPI as expected.

Re: Set expression in Min() leads to '-'

Thu, 17 Mar 2016 15:23:35 GMT

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.

Re: Set expression in Min() leads to '-'

Thu, 17 Mar 2016 15:29:18 GMT

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 ?

Re: Set expression in Min() leads to '-'

MK_QSL — Thu, 17 Mar 2016 15:30:45 GMT

filter_date must have DD.MM.YYYY format...Otherwise you need to format as below

Min({$<filter_date = {"$(=Date(Floor(Max({$<[filter_date]=>} discoverydate)), filter_date_format))"} price)

Re: Set expression in Min() leads to '-'

sunny_talwar — Thu, 17 Mar 2016 15:31:13 GMT

So you are unable to see the min price where filter_date = 16.02.2016? when you use your Min() expression?

Re: Set expression in Min() leads to '-'

sunny_talwar — Thu, 17 Mar 2016 15:39:59 GMT

You are using it in Aggr() function, I think you need to use it in Min:

count(

{ $ <calc_merchant = {"OWN_CLIENT_DATA_FLAG"},

[filter_date]= > }

distinct if(

aggr(rank(-min( { $ <filter_date = {'$(=date(floor(Max( {$<[filter_date]=>} discoverydate))))'} > }price),1,1), product_id, calc_merchant) = 1, product_id

)

Re: Set expression in Min() leads to '-'

Thu, 17 Mar 2016 15:44:02 GMT

I made 2 Screenshots, because I am still unable to share any data (NDA and size)

1. text.string inside the Set expression works:

2. function in the set expression does not work:

Re: Set expression in Min() leads to '-'

MK_QSL — Thu, 17 Mar 2016 15:47:00 GMT

Can you provide sample application?

Re: Set expression in Min() leads to '-'

Thu, 17 Mar 2016 15:52:36 GMT

As mentioned above i use real client data which is "secret".

Thus (and because of the app-size) I cannot attach an app.

Sorry for that, I know this would made things much more easy.

Big Thx for your help so far.

great community!

Re: Set expression in Min() leads to '-'

MK_QSL — Fri, 18 Mar 2016 09:35:28 GMT

Yes, we can understand that you can't provide original data, but obviously you can create some dummy date and post them in excel here !

Re: Set expression in Min() leads to '-'

Fri, 18 Mar 2016 15:47:59 GMT

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.