Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Hi Vlad,
Thanks.
That is an improvement however I would like to format the whole number e.g. to include commas.
Thanks,
Alan
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
Thanks Vlad - that's perfect.
Alan