Skip to Content
0

WEBI - Using MAX in a measure

Oct 25, 2017 at 09:45 AM

37

avatar image
Former Member

Hello,

I have two queries.

Query 1

ID

Max Date

(there is criteria, so that max date of when something happened which may not actually be the latest date)

Query 2

Unique ID

ID

Date

I am trying to create a variable to bring back the MAX Unique ID from query 2 where date in query 2 in the max date from query 1.

(because there could be more than one record on the date in query 2)

My variable keeps bringing back the MAX Unique ID bit not the max at when the dates are the same.

=Max([Query2].[UniqueID]) In ([Query2].[ID];[Query2].[Date]) Where ([Query2].[Date] = [Query1].[Max Date])

I know it has something to do with the context..I just haven't been able to figure out the order of the where, In, for each..etc.

10 |10000 characters needed characters left characters exceeded

can you share some sample data plz.

0
Former Member
AMIT KUMAR

Hello Amit,


Here is the data.

data.png (9.1 kB)
0
* Please Login or Register to Answer, Follow or Comment.

1 Answer

AMIT KUMAR
Oct 25, 2017 at 01:38 PM
0

try this.

=If ([Query 1].[Max date]=[Date]) Then Max([Unique ID]) In ([ID])

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

Is that syntax even correct? - sorry doesn't work. :-(

0

i have tried with merging Max date and date objects from both query.

you can change according to your report.

try this or do some changes

=If ([Query 1].[Max date]=[Query 2].[Date]) Then Max([Query 2].[Unique ID]) In ([ID])

0
Former Member
AMIT KUMAR

It just that never seen the 'then' before. The formula was accepted but returned no results. I will continue to play around with the orders.

1
Former Member
AMIT KUMAR

The merged dimensions are only ID - nothing else.

0

try with creating Unique ID as detail variable of ID object and then use in the formula

0
Former Member
AMIT KUMAR

Ok I will also try my original formula with detail variables.

0
Former Member
AMIT KUMAR

OK, if we can step back to your original answer - I think I was using the incorrect fields (they are similar in name)

=If ([Query 1].[Max date]=[Query 2].[Date]) Then Max([Query 2].[Unique ID]) In ([ID])

This does bring back a result but a #multivalue error.

0
Former Member

Hi RW Hamilton,

Please go through the below link for #multivalue error and also check whether you are getting correct data or not

Regards,

Anish

0
Former Member
Former Member

I didn't see a link but in the end I just modified the SQL. Thank for your help.

0