Skip to Content
0

How to get MIN and MAX dates in a table in Webi when table have a break?

Sep 29, 2017 at 02:12 PM

203

avatar image
Former Member

Hi All,

Hope everyone is doing well. I have a situation - created a report in webi where I have patient data which is divided into two part POST-Test & PRE-Test, each part have multiple Drawn DTTM (multiple results) and one single Dispense Event DTTM. The business user doesn't want all the drawn date times or result but wants the Minimum drawn datetime for POST-Test and Maximum drawn datetime for PRE-Test. I have attached a pic and in it I want only the row which are highlighted in yellow not the rest of the info.

I am open for solution at Universe level too - Information Design Tool

Please check:cusersk703370desktopscenario.png

Environment - Web Intelligence 4.1 SP 8, IDT 4.1, & Database Oracle.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

avatar image
Former Member Sep 29, 2017 at 04:23 PM
1

Hello Ahmed,

Maybe you should try creating a variable "FilterDate":

=([Pre\Post]="PRE" AND [Drawn DTTM]=(Max([Drawn DTTM]) In([Patient]))) OR ([Pre\Post]="POST" AND [Drawn DTTM]=(Min([Drawn DTTM]) In([Patient])))

While [Patient] is the patient field.

Filter the table for [FilterDate]=1, see if it helps.

Best Regards,

Or.

Show 3 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Ben,

It didn't help, when I apply filter everything goes away. The above variable doesn't give 1 when applied as filter in the table. Let me know if you have any other suggestions.

0

Hi Ahmed,

Above solution should work. Could you please check the In field in clause & formula created. I tried out in a similar scenario it was returning result as expected.

0
Former Member

Hi Or,

Tried, still doesn't work for me. Yes the variable doesn't give me the option to choose 1. Thanks for the help, let me know if you have anything else. Thanks once again.

0
avatar image
Former Member Oct 03, 2017 at 11:50 PM
0

Hi All,

I want to thank everyone who tried generously to help me out. I found the solution for the situation. Here it is as follows:

I created three variables:

1. Max Accession: =Max([Accession]) Where ([Variables].[Pre/Post] = "PRE") In ([Patient Birth Date])

2. Min Accession: =Min([Accession]) Where ([Variables].[Pre/Post] = "POST") In ([Patient Birth Date])

3. Accession Min/Max= If ([Accession]=[Min accession])Then 1 ElseIf ([Accession] = [Max accession]) Then 2 Else 0
( this will give 1 to the min accession, then 2 to max and finally 0 to the rest of them)

Last step: I applied the third variable to the table with condition to select all which are greater than 0. Thanks to God it worked perfectly.

Note:
1. The accession in the pre and post breaks are kind of numbers low to high
2. I gave Patient Birth Date as we don't have patient name or MRN in DEV environment, will replace it with patient name in PROD.

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Jan 31 at 11:18 PM
0

Hi All,

I want to thank everyone who tried generously to help me out. I found the solution for the situation. Here it is as follows:

I created three variables:

1. Max Accession: =Max([Accession]) Where ([Variables].[Pre/Post] = "PRE") In ([Patient Birth Date])

2. Min Accession: =Min([Accession]) Where ([Variables].[Pre/Post] = "POST") In ([Patient Birth Date])

3. Accession Min/Max= If ([Accession]=[Min accession])Then 1 ElseIf ([Accession] = [Max accession]) Then 2 Else 0
( this will give 1 to the min accession, then 2 to max and finally 0 to the rest of them)

Last step: I applied the third variable to the table with condition to select all which are greater than 0. Thanks to God it worked perfectly.

Note:
1. The accession in the pre and post breaks are kind of numbers low to high
2. I gave Patient Birth Date as we don't have patient name or MRN in DEV environment, will replace it with patient name in PROD.

Share
10 |10000 characters needed characters left characters exceeded