topic Re: Dollar AMount Not Formatting as Expected in QlikView

Dollar AMount Not Formatting as Expected

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

Hello:

I have the following as Text in Chart for a Gauge Chart and it is not formatting as I would expect it to.

=Num(Sum({$<CIO = {'Smith'}>} RangeMax([Finance Confirmed 2016 Savings], 0)), '$###0.00') & ' Of Goal'

The number I'm trying to format is 43175139.08 and the formatted result I'm looking for is $43.22. I realize I probably have too many # signs, but I've tried multiple different combinations, none of which have worked. With $###0.00, the result I get is $43175139.08.

Thanks in advance.

Re: Dollar AMount Not Formatting as Expected

vishsaggi — Tue, 03 Jan 2017 22:20:08 GMT

Can you try this?

=Num(Sum({$<CIO = {'Smith'}>} RangeMax([Finance Confirmed 2016 Savings], 0)), '$#0.00') & ' Of Goal'

Re: Dollar AMount Not Formatting as Expected

sunny_talwar — Tue, 03 Jan 2017 22:37:54 GMT

If your number is as you say it is, I see no reason why your formatting should not work

Are you sure you don't have to multiple your expression by 1000000 to get what you want?

Re: Dollar AMount Not Formatting as Expected

pnn44794 — Tue, 03 Jan 2017 22:39:03 GMT

Hello Vishwarath:

Thanks for the reply. I get the same result. Interestingly, if I provide an invalid CIO name, I don't get a sum, but it is in the right format. For example, it formats as $0.00.

If you happen to reply again, I will check it out in the morning. I have an appointment I need to go to now. Thanks again.

Re: Dollar AMount Not Formatting as Expected

pnn44794 — Tue, 03 Jan 2017 22:40:20 GMT

Hello Sunny:

Shoot. I think you're right. I have to multiply by 1 million. Good catch. I will definitely test this in the morning.

Re: Dollar AMount Not Formatting as Expected

sunny_talwar — Tue, 03 Jan 2017 22:40:52 GMT

What do you see when you use just this in a text box object?

=Sum({$<CIO = {'Smith'}>} RangeMax([Finance Confirmed 2016 Savings], 0))

Re: Dollar AMount Not Formatting as Expected

sunny_talwar — Tue, 03 Jan 2017 22:41:08 GMT

Sounds like a plan

Re: Dollar AMount Not Formatting as Expected

pnn44794 — Wed, 04 Jan 2017 22:13:00 GMT

Hello Sunny:

Sorry for the delay in replying. One of those days. Anyway, I actually have to divide by 1000000. It then works using the format Vishwarath provided. I'll tag your reply about multiplying as correct because it made remember I needed to divide by 1MM. Thanks again.