topic Re: Using Money() with Class() in QlikView

Using Money() with Class()

Tue, 01 Jul 2014 15:17:39 GMT

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,

Re: Using Money() with Class()

vgutkovsky — Tue, 01 Jul 2014 16:44:16 GMT

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

Re: Using Money() with Class()

Thu, 03 Jul 2014 15:59:09 GMT

Hi Vlad,

Thanks.

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

Thanks,

Alan

Re: Using Money() with Class()

vgutkovsky — Thu, 03 Jul 2014 16:21:18 GMT

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

Re: Using Money() with Class()

Thu, 03 Jul 2014 17:13:42 GMT

Thanks Vlad - that's perfect.

Alan