Qlik Community

Qlik Sense App Development

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

Not applicable

Set expression in Min() leads to '-'

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?

1 Solution

Accepted Solutions
Not applicable

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

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.

17 Replies
MVP
MVP

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

What is your date field format Edgar?

UPDATE: Try this

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

Not applicable

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

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 :/

MVP
MVP

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

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)

Not applicable

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

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.

MVP
MVP

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

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

What is the format of discoverydate and filter_date?

Not applicable

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

I get the 16.02.2016 as KPI as expected.

Not applicable

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

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.

Not applicable

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

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 ?