Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
khanashique
Contributor II

Set expression date less than today

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.

Labels (3)
1 Solution

Accepted Solutions

Re: Set expression date less than today

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

Re: Set expression date less than today

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

Re: Set expression date less than today

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
Contributor II

Re: Set expression date less than today

Thanks it worked...