Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

nawazuddin234
New Contributor III

Limit to two places after decimal in Amount Field

Hi All,

When I export the Amount  from the report to excel sheet its showing more than two places after the decimal which doesn't make sense .I checked the data in database there its only two places after the decimal. Can anyone please suggest me .Please find the attachments for better understanding.

Report capture.PNG

Measure I used :

Money( 

               

            if(WildMatch([Paid Range New],'>$500M'),

               sum({<[Supplier]={"=Sum(amount)>=500000000"}>}$(vAmount)),         

                 if(WildMatch([Paid Range New],'$500M - $100M'),

                  sum({<[Supplier]={"=500000000>sum(amount) and 100000000<sum(amount)"}>}$(vAmount)),         

                   if(WildMatch([Paid Range New],'$100M - $50M'),

                  sum({<[Supplier]={"=100000000>=sum(amount) and 50000000<sum(amount)"}>}$(vAmount)),

if(WildMatch([Paid Range New],'$50M - $10M'),

                  sum({<[Supplier]={"=50000000>=sum(amount) and 10000000<sum(amount)"}>}$(vAmount)),

if(WildMatch([Paid Range New],'$10M - $1M'),

                  sum({<[Supplier]={"=10000000>=sum(amount) and 1000000<sum(amount)"}>}$(vAmount)),

if(WildMatch([Paid Range New],'$1M - $500K'),

                  sum({<[Supplier]={"=1000000>=sum(amount) and 500000<sum(amount)"}>}$(vAmount)),

if(WildMatch([Paid Range New],'$500K - $400K'),

                  sum({<[Supplier]={"=500000>=sum(amount) and 400000<sum(amount)"}>}$(vAmount)),

if(WildMatch([Paid Range New],'$400K - 100K'),

                 sum({<[Supplier]={"=400000>=sum(amount) and 100000<sum(amount)"}>}$(vAmount)),

if(WildMatch([Paid Range New],'$100K - $25K'),

                 sum({<[Supplier]={"=100000>=sum(amount) and 25000<sum(amount)"}>}$(vAmount)),

if(WildMatch([Paid Range New],'$25K - $2500'),

                 sum({<[Supplier]={"=25000>=sum(amount) and 2500<sum(amount)"}>}$(vAmount)),

if(WildMatch([Paid Range New],'$2500 - $100'),

                  sum({<[Supplier]={"=2500>=sum(amount) and 100<sum(amount)"}>}$(vAmount)),

if(WildMatch([Paid Range New],'$100 - $0'),

                  sum({<[Supplier]={"=100>=sum(amount) and 0<sum(amount)"}>}$(vAmount)),

if(WildMatch([Paid Range New],'<$0'),

                  sum({<[Supplier]={"=0>=sum(amount)"}>}$(vAmount))     

)))))))))))))       

     

         /$(vAmountDivisor),$(vFormat))

TIA

8 Replies
zebhashmi
Valued Contributor

Re: Limit to two places after decimal in Amount Field

Problem is loading data or exporting

Try in Load

Num(field, '###.00") as Field1

Regards

Digvijay_Singh
Honored Contributor III

Re: Limit to two places after decimal in Amount Field

It could be a bug If you are saying source data has 2 decimal and Qlik is showing 3 decimal after export(provided there is no calculation between load to export in the field). You may like to re-check if all is fine with the source data.

nawazuddin234
New Contributor III

Re: Limit to two places after decimal in Amount Field

‌Hi,

Thanks for the prompt reply ,how do I prove to end users that it could be a bug ,

thank you .

Digvijay_Singh
Honored Contributor III

Re: Limit to two places after decimal in Amount Field

Whats your source system where you see only 2 decimal places?

Re: Limit to two places after decimal in Amount Field

You are dividing by

  /$(vAmountDivisor)

wouldn't that in some cases give you more than two decimal places? 

You are applying the Money() function to format.  That will limit the decimal places in the display format, but not the underlying number.  If you want to limit the actual number, apply a Round(...,.01) function as well in your expression within the Money().  e.g.

Money(Round(.....

.01),$(vFormat))

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

nawazuddin234
New Contributor III

Re: Limit to two places after decimal in Amount Field

Awesome it worked ,

Thank You so much

nawazuddin234
New Contributor III

Re: Limit to two places after decimal in Amount Field

Mr Digvijay Singh,

Thanks for your effort

Re: Limit to two places after decimal in Amount Field

Please mark something as "Correct" if you have a resolution.

-Rob

Community Browser