topic Re: if then else returning conflciting results in QlikView

if then else returning conflciting results

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: if then else returning conflciting results

sunny_talwar — Tue, 16 Aug 2016 16:11:59 GMT

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

Re: if then else returning conflciting results

Tue, 16 Aug 2016 16:12:40 GMT

in an expression

Re: if then else returning conflciting results

sunny_talwar — Tue, 16 Aug 2016 16:14:39 GMT

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?

Re: if then else returning conflciting results

Tue, 16 Aug 2016 16:20:51 GMT

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.

Re: if then else returning conflciting results

sunny_talwar — Tue, 16 Aug 2016 16:22:33 GMT

Yes

Re: if then else returning conflciting results

Tue, 16 Aug 2016 16:23:28 GMT

I get an error saying

error in expression: nested aggregation not allowed

Re: if then else returning conflciting results

sunny_talwar — Tue, 16 Aug 2016 16:28:52 GMT

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

Re: if then else returning conflciting results

Tue, 16 Aug 2016 16:31:34 GMT

customer

Re: if then else returning conflciting results

sunny_talwar — Tue, 16 Aug 2016 16:33:45 GMT

Try this:

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

Re: if then else returning conflciting results

Qrishna — Tue, 16 Aug 2016 16:36:21 GMT

Post some sample data.

Re: if then else returning conflciting results

Tue, 16 Aug 2016 16:38:56 GMT

getting the same results. Only the records with no historical data

Re: if then else returning conflciting results

Tue, 16 Aug 2016 16:40:48 GMT

Hi,

thanks for getting in touch.

What data do you want and how do I get I to you

Re: if then else returning conflciting results

Qrishna — Tue, 16 Aug 2016 19:09:13 GMT

you put some sample data i an qvw with your expression and logic and atatch it.

Re: if then else returning conflciting results

Wed, 17 Aug 2016 07:11:44 GMT

please excuse my ignorance but I don't know how to do that