Using Money() with Class()

[#1]

Hi,

I am using the Class function to group items based on their budget. I then use the Replace function to remove the default text which QV inserts and the dual function to sort. Please see my calculated Dimension below:

Dual(Replace(Class(Budget, 500000), '<= x <', '-'), Class(Budget, 500000))

The problem is my Dimension displays like this:

1000000 - 1500000

500000 - 1000000

0 - 500000

I really need it to look like this:

€1,000,000 - €1,500,000

€500,000 - €1,000,000

€0 - €500,000

However any attempt to format with the Money function has been fruitless.

Can this be done??

Thanks,

A

[vgutkovsky]

Well, since you're already formatting as dual, the first component is just text. You can just add a couple Euro symbols in like so:

dual(

        '€' & replace(class(Budget, 500000), '<= x <', '-€'),

        class(Budget, 500000)

)

Does that work?

Vlad

[#3]

Hi Vlad,

Thanks.

That is an improvement however I would like to format the whole number e.g. to include commas.

Thanks,

Alan

[vgutkovsky · Accepted solution]

Just a slight formatting addition:

dual(

        num(num#(trim(left(class(Budget, 500000),index(class(Budget, 500000),'<')-1))),'€#,##0')

        & ' - ' &

        num(num#(trim(mid(class(Budget, 500000),index(class(Budget, 500000),'<',2)+1))),'€#,##0')

        ,class(Budget, 500000)

)

I haven't tried it, but I think that's the right formula. Let me know if it works for you.

Cheers,

Vlad

[#5]

Thanks Vlad - that's perfect.

Alan