Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Specialist
Specialist

Problem is loading data or exporting

Try in Load

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

Regards

Digvijay_Singh

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.

Anonymous
Not applicable
Author

‌Hi,

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

thank you .

Digvijay_Singh

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Anonymous
Not applicable
Author

Awesome it worked ,

Thank You so much

Anonymous
Not applicable
Author

Mr Digvijay Singh,

Thanks for your effort

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

-Rob