Function within Set Analysis - help needed!

[#1]

Hi All,

I am looking to set up a straigfht table which will display the time logged to a system on a rolling 5 month basis.

The field which contains the time period is called 'YearMonth'. It is a dual field so it has a numeric value (to allow sorting etc) and a text representation e.g. '2014-Mar'.

I would like to use a function within a Set Analysis statement to do this, however the below set analysis is not functioning as required. I need the Time field to be summed where  the YearMonth field is equal to the maximum YearMonth value.

Sum ( {<YearMonth = {"$(=max( {1} YearMonth))" }>} Time)

More specifically the issue can be narrowed to this bit {"$(=max( {1} YearMonth))" }>}.

A zero value is returned.

There must be a syntax error which is not being flagged by the checker. Can anyone shed any light?

Apologies but due to company policy I cannot share the app.

Thanks,

A

[jerem1234 · Accepted solution]

It's because the max function converts the YearMonth into a number and you need the text representation for set analysis. Therefore you need to convert your number back into the equivalent text representation of YearMonth. You can try with date like:

{"$(=date(max( {1} YearMonth), 'YYYY-MMM'))" }

or use another function to get the text representation.

Hope this helps!

[#3]

Try

Sum ( {<YearMonth = {'$(=max(YearMonth))' }>} Time)

like I am suing in my application as below..

sum({<SalesMonthYear= {'$(=Max(SalesMonthYear))'}>}Net_Trading_Days)

[#4]

D'oh!

Thanks very much.

A