Date-Filterbox not working - Invalid dimension

[george55]

Hi all,

Would like to use a Date-Filterbox with a dimension containing timestamps (DATEFIELD1). When I use this formula, it works:

If(DATEFIELD1 > Today()-3), '-3 Days')

but with this formula not (got an invalid dimension):

If(DATEFIELD1 > FirstWorkDate(Max(Floor(DATEFIELD1)), 3, $(Holiday)), '-3 Days')

Below you can see the different datatypeformats:

Formula / Field	Num (Zahl)	Zahl	Datum	Dauer	Automatisch
Max(DATEFIELD1)	43857.38	43857.38	27.01.2020	1052577:11:07	27.01.2020 09:11
FirstWorkDate(Max(Floor(DATEFIELD1)), 5, $(Holiday))	43851.00	43851.00	21.01.2020	1052424:00:00	21.01.2020
Today()-1	43857.00	43857.00	27.01.2020	27.01.2020	43857

Previously thought there is a datatype-issue. Tried many converting-variations but with no success. Also when I'm doing a simple calculation, the result is a valid number: 

Max(DATEFIELD1) - FirstWorkDate(Max(Floor(DATEFIELD1)), 5, $(Holiday))

So I'm now confused and don't know what else to do. 

Thanks for any helpful information.

George

[Gysbert_Wassenaar · Accepted solution]

Try this: If(DATEFIELD1 > FirstWorkDate($(=Max(Floor(DATEFIELD1))), 3, $(Holiday)), '-3 Days')

[george55]

Thanks Gysbert, it worked!

Can you explain what the idea is behind this aspect? Thank you again.

[Gysbert_Wassenaar]

In a filter pane you either need to calculate the max date outside the context of the object or you need to use the aggr function so you get a result value for each dimension value of the dimension you use in the filter pane. In this case the first option is the correct solution. By using the $(=...expression..) syntax the max date is calculated at the document level instead of being evaluated (and failing in this case) at the row level of the filter pane dimension.

[george55]

thought something simliar it has to be with the different "levels" of data (document type). Was just confused why it worked with Today(). So that means if only use a function like Today(), I stay on same level (context of the object)?

[Gysbert_Wassenaar]

Today() just returns today's date. It doesn't use any dimension values to calculate the result.

[george55]

I do now have the next issue. May be is better to open new Thread here?

The filter pane is working fine, and the weekenddays are excluded, but not the associated dimensions. the dimensions have still the weekend inkluded.

Sure I could exclude this in the load script, but want to avoid that, because in some situations it is usefull to have the weekend included.

Thanks for any further advice.

[Gysbert_Wassenaar]

A new thread is better. Better yet is searching first. Your problem has likely been solved already a couple of times. Sounds like perhaps creating a flag field for the dates that are in weekends would work:

if(WeekDay(DATEFIELD1)>5,1,0) as IsWeekend