topic Re: Format Class ranges as currency in QlikView

Format Class ranges as currency

stubarnes — Fri, 31 Aug 2018 08:36:11 GMT

Hi I have the following but would like the formatting of numbers to be displayed as currency

=Dual(Replace(Class(TP,500),'<= x <','-'),Class(TP,500))

I tried inserting Money('£#,##0') before replace but did not work..

Any ideas?

Re: Format Class ranges as currency

Gysbert_Wassenaar — Fri, 31 Aug 2018 16:23:27 GMT

Perhaps something like this:

Dual( Money( Floor(TP,500) , '£#,##0') & '-' & Money( Ceil(TP,500) , '£#,##0') , Floor(TP,500))

Re: Format Class ranges as currency

MarcoWedel — Fri, 31 Aug 2018 22:30:42 GMT

Hi,

some solutions might be:

=Dual('£'&Replace(Class(TP,500),' <= x < ',' - £'),Class(TP,500))

=Dual(Money(Floor(TP,500),'£#,##0')&'-'&Money(Floor(TP,500)+500,'£#,##0'),Floor(TP,500))

hope this helps

regards

Marco

Re: Format Class ranges as currency

BalaBhaskar_Qlik — Sun, 02 Sep 2018 14:49:22 GMT

May be this:

=Dual(Replace(Class(TP,500),'<= x <','to'), Num(Class(TP,500)))

Re: Format Class ranges as currency

BalaBhaskar_Qlik — Sun, 02 Sep 2018 14:50:07 GMT

Check this:

Using Money() with Class()

Re: Format Class ranges as currency

stubarnes — Mon, 03 Sep 2018 10:54:41 GMT

Thanks for your help on this, works perfectly!

If I could ask an additional question, is it possible to format the numbers as a percentage #,##0.0%

Re: Format Class ranges as currency

Gysbert_Wassenaar — Mon, 03 Sep 2018 13:44:30 GMT

Yes, with the num function instead of the money function.

Re: Format Class ranges as currency

MarcoWedel — Mon, 03 Sep 2018 20:06:30 GMT

Hello gwassenaar,

I noticed a difference in the results of the Class() vs. Floor()/Ceil() approach due to Floor and Ceil both letting integer values unchanged. Can you check?

thanks

regards

Marco

Re: Format Class ranges as currency

Gysbert_Wassenaar — Tue, 04 Sep 2018 04:41:39 GMT

Yeah, if a value falls exactly on a border value then an incorrect result is returned. Your expression always returns the correct result.