topic Error In Number Format Within Expression in QlikView

Error In Number Format Within Expression

pnn44794 — Wed, 25 Nov 2020 16:16:04 GMT

Hello:

I have the following expression which is used in a Segment for a Gauge chart. There are no "errors" in the expression as identified by Qlikview. However, the dollar amount I'm expecting to see while correct, does not format as expected. The expected format is $###. For example, I expect to see the number $192, but instead I get (no dollar sign) 192.4339230. Here's the expression:

=Num(Sum({$<[Phase] = {Booked}, Phase, ID, [Item Owner], Submitter, [Item Validator], Confidence, [MYP Work Stream], [Save Initiative Category], [Sub Category], [Start Month], [Booking Month], [End Month]>} RangeMax([Finance Confirmed 2016 Savings], 0)/1000000),'$###0') + Num(Sum({$<[Phase] = {'Finance*', Implementation}, Confidence = {High}>} [Estimated Save]) /1000000 ,'$###0')

Any ideas anyone? The objective, if it helps, is to get a total of everything Booked plus everything in Finance* and Implementation where the Confidence level is High. I'll then need to replicate this for a couple of other scenarios.

Lastly, the reason I have a bunch of fields listed is in case someone filters for one of those fields. Without listing them, the numbers are not correct. As always, thanks in advance.

Re: Error In Number Format Within Expression

lironbaram — Sat, 24 Dec 2016 05:24:22 GMT

just use the number function once and it'll work

the reason that when you add to different numbers Qlikview doesn't know which format to use on the combine number

and goes to default

=Num(Sum({$<[Phase] = {Booked}, Phase, ID, [Item Owner], Submitter, [Item Validator], Confidence, [MYP Work Stream], [Save Initiative Category], [Sub Category], [Start Month], [Booking Month], [End Month]>}RangeMax([Finance Confirmed 2016 Savings], 0)/1000000)+ Sum({$<[Phase] = {'Finance*', Implementation}, Confidence = {High}>} [Estimated Save]) /1000000 ,'$###0')

Re: Error In Number Format Within Expression

vinieme12 — Sat, 24 Dec 2016 07:35:50 GMT

Try

=NUM( (Sum({$<[Phase] = {Booked}, Phase, ID, [Item Owner], Submitter, [Item Validator], Confidence, [MYP Work Stream], [Save Initiative Category], [Sub Category], [Start Month], [Booking Month], [End Month]>} RangeMax([Finance Confirmed 2016 Savings], 0) + Sum({$<[Phase] = {'Finance*', Implementation}, Confidence = {High}>} [Estimated Save]) )/1000000,'$###0')

Re: Error In Number Format Within Expression

pnn44794 — Sat, 24 Dec 2016 19:46:34 GMT

A big thanks Liron as that did indeed work! I was over complicating it. 🙂 Thank you again and thanks for the explanation as that was really helpful.

Re: Error In Number Format Within Expression

pnn44794 — Sat, 24 Dec 2016 20:00:49 GMT

Hello Vineeth:

Thanks for your reply. There is an error with it and I believe it's because of too many parentheses. I think the first one right after NUM is not needed. The expression editor says "Token don't match" and that parentheses is highlighted.

There's another error as well, but I'm not sure what it is. The text box where I tested it states "Error: Error in expression: Nested aggregation not allowed".

I appreciate you taking the time to reply. Again, thanks.

P.S. The only reason I mention the errors is in case someone else reads this thread and tries to use your expression. I didn't have it right either.