BusinessObjects Board

A date compared with two dates

Compare dates.pdf (55.9 KB)

Hello all,
I bring in data into webi through an excel file. In sheet 1 I have a date, date 1, which I want to check with against two dates, date 2 and date 3, in sheet 2. The check is to see if date 1 is between date 2 and date 3. The respective dates exist in two different excel sheets (in the same excel file).
I have attached an example as pds to this topic.
I hope that a kind soul can help.

Thank you and good day everyone.

Have you tried merging your queries on Navn Arbejstager? I’ll work up an example.

So I mocked up a sample spreadsheet as I understand your data with an additional row (Ms Y) to show a date that is not in range. Here are my resulting tables…

image

If you try to add Dato from the first query to the table corresponding to the second query you will get something like this…

image

The key here is you need merge on the common object, Navn Arbejstager, in your case. To do so, click on it in the first query, Ctrl+click on it in the second query, right-click, and choose “Merge”.

image

Next you need to create a detail variable equal to Dato with Navn Arbejstager as the “Associated dimension”.

It is important that you choose the merged instances of Navn Arbejstager and not the instance from either query.

Now you can create a variable that checks if Dato is in range…

=If([Var Dato] Between ([elnd fraDato]; [elnd tilDato]); 1; 0)

image

There are more things to think about like what if a Navn Arbejstager is in one sheet, but not the other. Those scenarios can be dealt with, but I just wanted to stick to the basics since that may not be an issue for you.

I have referred to the following blog post numerous times when I have gotten stuck on something related to merging. Check it out.

Hope this helps,

Noel

1 Like

Hello Noel,
Thank you very much for your reply. I tried it. It didn’t work. I have attached my result
Compare dates 2.pdf (78.6 KB)
.

I am assuming all of this variables are your various attempts to get what you want. The last one should work. Can you provide a screen shot of your variable with the Qualification set to Detail and the Associated dimension set to the your merged dimension?

Hi Noel,
Thank you. The information you asked for is attached in document.
Compare dates 3.pdf (174.5 KB)

I cannot make sense of all your screenshots and formulas. You need to give some sort of explanation. What I originally describe will work to be able to compare the dates from two queries (spreadsheets) where there is one common object which gets merged. You merged at least 4 sets of objects.

I am not sure I can help you.

Noel

I think your else is at the wrong location.

=If ([Datofra- M-Ă…] =[eInd fraDato - M-Ă…]
Or
[Datotil- M-Ă…]=[eInd tilDato - M-Ă…])
Then (
If(
[Datofra - D-M-Ă…] Between ([eInd fraDato - D-M-Ă…];[eInd tilDato - D-M-Ă…]) )
Then “1” Else “0”
) else "0"