ONLY and max month

[Anonymous]

Month	Total Number of Traffic
2016-01	100
2016-02	200
2016-03	300
2016-04	400

above is the table where my data is. Every month gets a new row. I want max month and its Total number of Traffic in a text box.

for eg, currently I want 'April 2016 400' displayed ina the text box. how do I do that

[Anonymous]

I have variable vmax = =max(Month(Month))

and then I have the below expn but its not working.

=only({<Month ={'$vmax)'}>}[Total Number of Traffic])

[sunny_talwar]

May be try this:

=Only({<Month ={'$(=vmax)'}>}[Total Number of Traffic])

or

=FirstSortedValue([Total Number of Traffic], -Month)

[stevedark]

I would probably go for either Sum or Max instead of Only - to deal with the case where more than one row turns up in a month:

=Sum({<Month={'$(vmax)'}>}[Total Number of Traffic])

Also, you may need to ensure that your vmax variable is returning the month in the correct format:

=Date(Max(Month), 'YYYY-MM')

Using the Month function around the Month field would return just 4 for April this year, rather than 2016-04.

Hope that helps.

Steve

[Anonymous]

hi Steve and Sunny, Thank you for the reply. I tried both solutions and first sorted value worked. However if possible,

I want to see sum/only function with max date variable work. I changed the date field to 1/1/2016 in my data source.

I have compared both functions in the QVW I have attached here, along with excel. any comments would be awesome.

[sunny_talwar · Accepted solution]

Try this:

=Sum({<Month={'$(=Date(vmax))'}>}[Total Number of Traffic])

[Anonymous]

Thank you again. That was it!

[stevedark]

Just to make you aware, that the Date function only works there as the natural date format of that field (how it appears if you just add it in a list box without formatting) matches the default date for your system.

If you change the date format to appear as you have in your table above, to 2016-04, which can be done in the load script, like this:

Date(Month, 'YYYY-MM') as Month,

You will then need to also insert the format pattern into the Set Analysis or Variable.

Glad it is now working.

Steve

[Anonymous]

Thanks Steve for your suggestions.