Min of date when flag is 1

[thakkarrahul01]

Hi Everyone,

I am trying to calculate minimum date from a field when only for the records where corresponding flag is set to 1.

I have used expression in text box as :

=only({<RecDate={"$(=date(min(RecDate)))"},Flag={1}>}RecDate)

I am getting output as dash. Can anyone please help.

Please find data attached.

Kind Regards,

Rahul

[MK_QSL]

=Date(FirstSortedValue({<Flag = {1}>}RecDate, RecDate))

[swuehl]

For the minimum date, just use

=Min({<Flag={1}>} RecDate)

or formatted

=Date(Min({<Flag={1}>} RecDate))

[Kushal_Chawda]

You can do the same in script to make expression simpler

Data:

LOAD date(RecDate,'DD/MM/YYYY') as  RecDate,

     Flag,

     Text

FROM

[Book1.xlsx]

(ooxml, embedded labels, table is Sheet1);

Left Join

LOAD date(min(RecDate),'DD/MM/YYYY') as RecDate,

'1' as MinDateFlag

Resident Data

where Flag=1;

Now write the expression

=only({<MinDateFlag={'1'}>}RecDate)

[thakkarrahul01]

swuehl‌ & @Manish

Perfect !!  Thanks a lot

[Anonymous]

what about this?

=IF(Flag=1, min(date))

[thakkarrahul01]

Yes, gives a simpler expression. I am avoiding a data model change for now.

Thanks Kush141087‌

[thakkarrahul01]

I tried this, It falls over and gives: 15/12/1958