Set expression date less than today

[khanashique]

Hi Experts,

Below is the set expression i am trying, first part is when "Choose Date" is null then expression has to return the max date's value which is available in the table, second part it when "Choose Date" is selected.

---------------------------------------------------------------------------------------

if(isnull(Choose_Date),(if((sum({<Choose_Date = {"$(=max(Choose_Date))"}>}POS_ISSUE_COUNT)+
sum({<Choose_Date = {"$(=max(Choose_Date))"}>}POS_RETURN_COUNT))
> 0,
if((sum({<Choose_Date = {"$(=max(Choose_Date))"}>}POS_ISSUE_COUNT)-
sum({<Choose_Date = {"$(=max(Choose_Date))"}>}OBK_ISSUE_COUNT))
+
(sum({<Choose_Date = {"$(=max(Choose_Date))"}>}POS_RETURN_COUNT)-
sum({<Choose_Date = {"$(=max(Choose_Date))"}>}OBK_RETURN_COUNT))=0,
POS_ISSUE_COUNT & ' / ' & POS_RETURN_COUNT, 'Wrong')))
,
(if((sum({<Choose_Date = {"$(=getfieldselections(Choose_Date))"}>}POS_ISSUE_COUNT)+
sum({<Choose_Date = {"$(=getfieldselections(Choose_Date))"}>}POS_RETURN_COUNT))
> 0,
if((sum({<Choose_Date = {"$(=getfieldselections(Choose_Date))"}>}POS_ISSUE_COUNT)-
sum({<Choose_Date = {"$(=getfieldselections(Choose_Date))"}>}OBK_ISSUE_COUNT))
+
(sum({<Choose_Date = {"$(=getfieldselections(Choose_Date))"}>}POS_RETURN_COUNT)-
sum({<Choose_Date = {"$(=getfieldselections(Choose_Date))"}>}OBK_RETURN_COUNT))=0,
POS_ISSUE_COUNT & ' / ' & POS_RETURN_COUNT, 'Wrong'))))

------------------------------------------------------------------------------------

Above expression works only for the second part and returns the correct value as expected, but the first part returns only string '/', kindly assist.

Attached is the sample data for reference.

Thanks,

MAK.

[sunny_talwar]

May be change the set analysis to the first part of your expression to this

{<Choose_Date = {"$(=Date(Max(Choose_Date), 'M/D/YYYY hh:mm:ss TT'))"}>}

[sunny_talwar · Accepted solution]

Try this

If(IsNull(Choose_Date),
(If((Sum({<Choose_Date = {"$(=Date(Max(Choose_Date), 'M/D/YYYY hh:mm:ss TT'))"}>}POS_ISSUE_COUNT)+
Sum({<Choose_Date = {"$(=Date(Max(Choose_Date), 'M/D/YYYY hh:mm:ss TT'))"}>} POS_RETURN_COUNT))
> 0,
if((sum({<Choose_Date = {"$(=Date(Max(Choose_Date), 'M/D/YYYY hh:mm:ss TT'))"}>}POS_ISSUE_COUNT)-
sum({<Choose_Date = {"$(=Date(Max(Choose_Date), 'M/D/YYYY hh:mm:ss TT'))"}>}OBK_ISSUE_COUNT))
+
(sum({<Choose_Date = {"$(=Date(Max(Choose_Date), 'M/D/YYYY hh:mm:ss TT'))"}>}POS_RETURN_COUNT)-
sum({<Choose_Date = {"$(=Date(Max(Choose_Date), 'M/D/YYYY hh:mm:ss TT'))"}>}OBK_RETURN_COUNT))=0,
Sum({<Choose_Date = {"$(=Date(Max(Choose_Date), 'M/D/YYYY hh:mm:ss TT'))"}>} POS_ISSUE_COUNT) & ' / ' & 
Sum({<Choose_Date = {"$(=Date(Max(Choose_Date), 'M/D/YYYY hh:mm:ss TT'))"}>} POS_RETURN_COUNT), 'Wrong')))
,
(if((sum(POS_ISSUE_COUNT)+
sum(POS_RETURN_COUNT))
> 0,
if((sum(POS_ISSUE_COUNT)-
sum(OBK_ISSUE_COUNT))
+
(sum(POS_RETURN_COUNT)-
sum(OBK_RETURN_COUNT))=0,
Sum(POS_ISSUE_COUNT) & ' / ' & 
Sum(POS_RETURN_COUNT), 'Wrong'))))

[khanashique]

Thanks it worked...