Dear friends,
I have made an expression to select list of rows with certain conditions ( This expression is used in filter dashboard to select values/ rows based on certain criteria).
I face some problem with the expression. I have used 4 conditions in IFS function in each block ( Totally 4 Blocks are there). Here in general argument is to take blank values/ rows or not.
Condition 1. ([_THISROW].[INCLUDE EMPTY ORDER DATE]="ONLY EMPTY"),ISBLANK([ORDER DATE]) ----- This works ( Selecting only blank values)
Condition2 : AND(ISBLANK([_THISROW].[ORDER START DATE]), ([_THISROW].[INCLUDE EMPTY ORDER DATE] = "YES")), ISBLANK([ORDER DATE]),
------------ Not working Expected. (Want to select blank values rows if this row order date is blank and include empty order date is = " YES"
Condition 3 : AND(ISBLANK([_THISROW].[ORDER START DATE]),ISBLANK([_THISROW].[INCLUDE EMPTY ORDER DATE])), TRUE,
----- Not Working as expected ( Want to select rows if 2 columns are blank in this table)
Condition 4:
(isnotblank([_THISROW].[ORDER START DATE])),
OR(
IFS(
([_THISROW].[INCLUDE EMPTY ORDER DATE]="YES"), ISBLANK([ORDER DATE])
),
([ORDER DATE]>=[_THISROW].[ORDER START DATE])
)
--- It returns only blank values ( Empty ) and doesn't works this sub expression ([ORDER DATE]>=[_THISROW].[ORDER START DATE])
( Want to select values if [_THISROW].[ORDER START DATE] is not blank and [_THISROW].[INCLUDE EMPTY ORDER DATE]="YES") the select BLANK ([ORDER DATE]. or ([ORDER DATE]>=[_THISROW].[ORDER START DATE]).
The expression full is here
select(CONSIGNMENTS[CONSIGNMENTS ID],
AND(
#Block 1
ifs(
([_THISROW].[INCLUDE EMPTY ORDER DATE]="ONLY EMPTY"),ISBLANK([ORDER DATE]),
AND(ISBLANK([_THISROW].[ORDER START DATE]), ([_THISROW].[INCLUDE EMPTY ORDER DATE] = "YES")), ISBLANK([ORDER DATE]),
AND(ISBLANK([_THISROW].[ORDER START DATE]),ISBLANK([_THISROW].[INCLUDE EMPTY ORDER DATE])), TRUE,
(isnotblank([_THISROW].[ORDER START DATE])),
OR(
IFS(
([_THISROW].[INCLUDE EMPTY ORDER DATE]="YES"), ISBLANK([ORDER DATE])
),
([ORDER DATE]>=[_THISROW].[ORDER START DATE])
)
),
# Block 2
ifs(
([_THISROW].[INCLUDE EMPTY ORDER DATE]="ONLY EMPTY"),ISBLANK([ORDER DATE]),
AND(ISBLANK([_THISROW].[ORDER END DATE]), ([_THISROW].[INCLUDE EMPTY ORDER DATE]= "YES")), ISBLANK([ORDER DATE]),
AND(ISBLANK([_THISROW].[ORDER END DATE]), ISBLANK([_THISROW].[INCLUDE EMPTY ORDER DATE])), TRUE,
(isnotblank([_THISROW].[ORDER END DATE])),
OR(
IFS(
([_THISROW].[INCLUDE EMPTY ORDER DATE]="YES"), ISBLANK([ORDER DATE])
),
([ORDER DATE]<=[_THISROW].[ORDER END DATE])
)
),
# Block 3
ifs(
([_THISROW].[INCLUDE EMPTY DISPATCH DATE]="ONLY EMPTY"), ISBLANK([DATE OF DISPATCH]),
AND(ISBLANK([_THISROW].[DISPATCH START DATE]), ([_THISROW].[INCLUDE EMPTY DISPATCH DATE] = "YES")), ISBLANK([DATE OF DISPATCH]),
AND(ISBLANK([_THISROW].[DISPATCH START DATE]),ISBLANK([_THISROW].[INCLUDE EMPTY DISPATCH DATE])), TRUE,
(isnotblank([_THISROW].[DISPATCH START DATE])),
OR(
IFS(
([_THISROW].[INCLUDE EMPTY DISPATCH DATE]="YES"), ISBLANK([DATE OF DISPATCH])
),
([DATE OF DISPATCH]>=[_THISROW].[DISPATCH START DATE])
)
,
# Block 4
IFS(
([_THISROW].[INCLUDE EMPTY DISPATCH DATE]="ONLY EMPTY"),ISBLANK([DATE OF DISPATCH]),
AND(ISBLANK([_THISROW].[DISPATCH END DATE]), ([_THISROW].[INCLUDE EMPTY DISPATCH DATE]== "YES")), ISBLANK([DATE OF DISPATCH]),
AND(ISBLANK([_THISROW].[DISPATCH END DATE]), ISBLANK([_THISROW].[INCLUDE EMPTY DISPATCH DATE])), TRUE,
(isnotblank([_THISROW].[DISPATCH END DATE])),
OR(
IFS(
([_THISROW].[INCLUDE EMPTY DISPATCH DATE]="YES"), ISBLANK([DATE OF DISPATCH])
),
([DATE OF DISPATCH]<=[_THISROW].[DISPATCH END DATE])
)
)
)
)
@AleksiAlkio @dbaum @Suvrutt_Gurjar please help me with this.
LinkedIn
Twitter
