[solved] Slight problem with day_of_week

[VisioX]

Hello Everyone,

I am working on a dashboard with several items on it.
One is a list with a filter.
If I set the filter to

Code Select Expand

[Dashboards].[UTILSUM].[UTEND] = _add_days(current_date,-1)
it works fine, but on Sundays and Mondays I have to subtract 2 or 3 days.

Filter set to

Code Select Expand

if (
1 = 1
) then (
[Dashboards].[UTILSUM].[UTEND] = _add_days(current_date,-3)
) else (
[Dashboards].[UTILSUM].[UTEND] = _add_days(current_date,-1)
)
works good, but that was just a test, still missing my Sunday/Monday solution.

Now it set the filter to

Code Select Expand

case _day_of_week(current_date,1)
when (1) then ([Dashboards].[UTILSUM].[UTEND] =_add_days(current_date,-3))
when (7) then ([Dashboards].[UTILSUM].[UTEND] =_add_days(current_date,-2))
else ([Dashboards].[UTILSUM].[UTEND] =_add_days(current_date,-1))
end

and get following errors...

Code Select Expand


UDA-QOS-0006 Error
UDA-SQL-0358 Line 20: Syntax error near "=".
QE-DEF-0459 CCLException
RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-16'.
UDA-SQL-0358 Line 20: Syntax error near "=".
UDA-QOS-0006 Error
UDA-SQL-0358 Line 20: Syntax error near "=".
QE-DEF-0459 CCLException
RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-16'.
UDA-SQL-0358 Line 20: Syntax error near "=".
RSV-VAL-0004 Unable to find query information for the item UTSTR1.
RSV-VAL-0004 Unable to find query information for the item UTEND1.
RSV-VAL-0004 Unable to find query information for the item UTRGN.
RSV-VAL-0004 Unable to find query information for the item UTCMAJ.
RSV-VAL-0004 Unable to find query information for the item UTLMAJ.
RSV-VAL-0004 Unable to find query information for the item UTCCON.
RSV-VAL-0004 Unable to find query information for the item UTLCON.
RSV-VAL-0004 Unable to find query information for the item UTCOWN.
RSV-VAL-0004 Unable to find query information for the item UTLOWN.
RSV-VAL-0004 Unable to find query information for the item UTCOTH.
RSV-VAL-0004 Unable to find query information for the item UTCALL.

No big deal, let's change the code to

Code Select Expand

if (
_day_of_week(current_date,1) = 1
) then (
[Dashboards].[UTILSUM].[UTEND] = _add_days(current_date,-3)
) else (
if (
_day_of_week(current_date) = 7
) then (
[Dashboards].[UTILSUM].[UTEND] = _add_days(current_date,-2)
) else (
[Dashboards].[UTILSUM].[UTEND] = _add_days(current_date,-1)
)
)

...well, same error code like the CASE version.

Do I miss something essential or is a list not allowed to use a simple _day_of_week function in a filter?



Thanks,
Guido

[blom0344]

You cannot use an eqaulity sign in if then else or CASE constructs in the then or else part.
For a given data item

[Dashboards].[UTILSUM].[UTEND]

the definition should be:

Code Select Expand


if (_day_of_week(current_date,1) = 1)
then (_add_days(current_date,-3))
else (if (
_day_of_week(current_date) = 7)
then (_add_days(current_date,-2))
else (_add_days(current_date,-1)))



Better use a CASE construct for multiple THEN parts, that is more readable (just a tip)

[VisioX]

All right!

I ended up with this:

Code Select Expand

[Dashboards].[UTILSUM].[UTEND] =
case _day_of_week(current_date,1)
when (1) then (_add_days(current_date,-3))
when (7) then (_add_days(current_date,-2))
else (_add_days(current_date,-1))
end

Works great, thanks!