Displaying Date

[Former Member]

Hi All Experts!

I have got in trouble to display date dimension. As you seen from below, I have two tables and I would like to show date that has to be matched related amount.

The report is based on single universe, one query and there is no more complex structure.

My current table:

Line	DATE	AMOUNT
1	01.03.2015	30
1	02.03.2015	50
1	03.03.2015	10
2	01.02.2015	20
2	02.02.2015	40
2	03.03.2015	5

I would like to get this table:

Line	DATE	MAX AMOUNT
1	01.03.2015	50
2	02.02.2015	40

any help would be appreciated!

[tanveer1 · Accepted solution]

Hi ,

Create one variable as

NewVar = Rank([Amount];[Line])

this will give you the rank based on the amount for each line

Add that variable in your table and filter it when it is 1. Hide the newVar column

Thanks,

Tanveer.

[Former Member · Accepted solution]

Hi Tanveer,

Thanks for replied it...It has worked on my requirement as well.. I have also fixed it up as I have shown below..

First,

I have created a variable that is maxamount = max([amount]in([month];[date]) and then I have applied for each function on my date column...= [Date] FOREACH [AMOUNT]

[Former Member]

Hi

  If you want to show only maximum amount then use RANK for amount column and  display top N.

[Former Member]

hi Tech,

If I do your way I have got duplicate data on the table.. However, on the second table I would like to get distinct value for each line...It does not work for this problem..

[saurabh_sonawane]

Hi ATACAN had u try my solution

[saurabh_sonawane]

hi try to create a new variable that is month

month = formatedate([date];"MM.yyyy")

create a new variable measure as max amount

maxamount = max([amount]in([month];[date])

and create a new variable final month

final month = formatdate([month];"01.MM.yyyy")

and finaly in table keep

finalmonth , month ,maxamount

NOte  hide the month field

[saurabh_sonawane]

or try this

month = formatedate([date];"MM.yyyy")

maxamount = max([amount]in([month];[date])

final date = [date] where [formatedate([date];"MM.yyyy") =[month] and [amount] = [maxamount]

and display only final date and maxamount

[Former Member]

Hi Saurabh,

As you have mentioned in your reply, I have done all steps of your solution. However, the line column has shown same as previous table (duplicated) after apply max function on amount.In addition, why you have changed the date format? I retrieve the date format like dd.mm.yyyy as I mentioned on the sample tables.

[saurabh_sonawane]

Plz send me a sreen shot bec i had try it is work

below thing i had try and it is working fine

hi try to create a new variable that is month

month = formatedate([date];"MM.yyyy")

create a new variable measure as max amount

maxamount = max([amount]in([month];[date])

and create a new variable final month

final month = formatdate([month];"01.MM.yyyy")

and finaly in table keep

finalmonth , month ,maxamount

NOte  hide the month field