if then else returning conflciting results

[#1]

Hi,

I have created an if then else statement as below

=if (monthstart(today())= Max([Template Date]), [Revenue Target Value])

the stamenet looks at the first day of the month(01/08.2016) and picks the matching template date(there are lots) and should return the target value record.

However, it returns -  for all records that have template date records of 01/01/2016, 01/02/2016 etc

If I change the if then else statement to

=if (monthstart(today())= Max([Template Date]), 1, 0)

it display a 1 for all records were the month start = the max template date

Hence it would sugges there is an issue around the revenue target value field

I'm lost

[sunny_talwar]

Where exactly are you using this if statement? Expression or dimension?

[#3]

in an expression

[sunny_talwar]

Is it possible there are multiple values per dimension? Try this and see if you get multiple values or just one value?

Concat(DISTINCT if (monthstart(today())= Max([Template Date]), [Revenue Target Value]), ', ')

If you get multiple values, how do you want to view them? Sum them, Avg them?

[#5]

Did I add this as an expression?

David Adlard

Reporting Manager

Excelsior Technologies Ltd

DDI: 01244 833237

Main: 01244 833230

Fax: 01244 833231

Email: DavidAdlard@exceltechuk.com<mailto:DavidAdlard@exceltechuk.com>

Twitter: @exceltech_uk

<http://www.exceltechuk.com/images/excelsior-logo.gif>

Please consider the environment before printing this email message

Disclaimer:

This message contains confidential information and is intended only for the named recipients. If you are not a valid recipient you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. If verification is required please request a hard-copy version.

Excelsior Technologies Ltd is a limited company registered in England and Wales. Registered number: 05188170. Registered office: Parkway, Deeside Industrial Park, Deeside, Flintshire, CH5 2NS, United Kingdom.

[sunny_talwar]

Yes

[#7]

I get an error saying

error in expression: nested aggregation not allowed

[sunny_talwar]

Yes, that is right. You have a max in there. I totally overlooked that. What is your chart dimension?

[#9]

customer

[sunny_talwar]

Try this:

Concat(DISTINCT Aggr(If(MonthStart(Today()) = Max([Template Date]), [Revenue Target Value]), Customer), ', ')