# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for
Did you mean:
Master

## wrong sum

Hi All,

tresesco

simone.spanio

i am using   pivot table

Dimension :

Product

Measure: 1   :

service  :

=If(sum({<SERVICE_TYPE={'Service'}>}Amount)/1000000000 > 1,

Num(round(sum({<SERVICE_TYPE={'Service'}>}Amount)/1000000000),'\$ ####B'),

If(sum({<SERVICE_TYPE={'Service'}>}Amount)/1000000>1,

Num(round(sum({<SERVICE_TYPE={'Service'}>}Amount)/1000000),'\$ ####M'),

Num(round(sum({<SERVICE_TYPE={'Service'}>}Amount)/1000),'\$ ####K')))

Measure2:

HW:

=If(sum({<SERVICE_TYPE={'Hardware'}>}Amount)/1000000000 > 1,

Num(round(sum({<SERVICE_TYPE={'Hardware'}>}Amount)/1000000000),'\$ ####B'),

If(sum({<SERVICE_TYPE={'Hardware'}>}Amount)/1000000>1,

Num(round(sum({<SERVICE_TYPE={'Hardware'}>}Amount)/1000000),'\$ ####M'),

Num(round(sum({<SERVICE_TYPE={'Hardware'}>}Amount)/1000),'\$ ####K')))

Measure 3:  this is total  sum(Measure1)+sum(Measure2)      or column1 +column2

=If(sum(USD_Amount)/1000000000 > 1,

Num(round(sum(Amount)/1000000000),'\$ ####B'),

If(sum(Amount)/1000000>1,

Num(round(sum(Amount)/1000000),'\$ ####M'),

Num(round(sum(Amount)/1000),'\$ ####K')))

My issue is   Measure3  Total  is not giving the correct result     you can refer the screen shot.   what is the issue?  can anyone help me on this?

Thanks

Sony

1 Solution

Accepted Solutions
Master II

Hi,

Try like this.

```SET vScaleNumber=if(\$1>1000000000, num(\$1/1000000000,'#,##0.000B'),if(\$1>1000000, num(\$1/1000000,'#,##0.000M'),if(\$1>1000, num(\$1/1000,'#,##0.000K'),num(\$1,'#,##0'))));
SET ServiceAndHardware =sum({<SERVICE_TYPE={'Service','Hardware'}>}Total_Opportunity_Value_USD_Amount);
```

Expression 1: Service

\$(vScaleNumber(sum({<SERVICE_TYPE={'Service'}>}Total_Opportunity_Value_USD_Amount)))

Expression 2: Hardware

\$(vScaleNumber(sum({<SERVICE_TYPE={'Hardware'}>}Total_Opportunity_Value_USD_Amount)))

Expression 3 : Total

if(\$(ServiceAndHardware)>1000000000, num(\$(ServiceAndHardware)/1000000000,'#,##0.000B'),if(\$(ServiceAndHardware)>1000000, num(\$(ServiceAndHardware)/1000000,'#,##0.000M'),if(\$(ServiceAndHardware)>1000, num(\$(ServiceAndHardware)/1000,'#,##0.000K'),num(\$(ServiceAndHardware),'#,##0'))))

10 Replies
MVP

did you try using SERVICE_TYPE as horizontal dimension instead of creating separate expressions each reducing SERVICE_TYPE to a single value with set expressions?

The total then could be calculated with the standard partial sum option.

hope this helps

Marco

Master
Author

i want to show  4 tables   to user s   --- all four tables giving me wrong totals

1 is By   Quarter

2 is by Region

3 Opp_type

4 is By  SS

Not by servicetype

Master
Author

if i remove the  if condition and  that num formating all

its giving me correct sum and correct total..   May be issues with Number formatting  how to change to tthe correct expression?

MVP

please post a sample application to demonstrate

Master
Author

PFA sample

MVP

How is your expected result different from the results your sample application delivers?

Master
Author

the same number should come  that is original data only  i exported to excel required columns and prepared the sample.

total should give me the correct

Master II

Hi,

Try like this.

```SET vScaleNumber=if(\$1>1000000000, num(\$1/1000000000,'#,##0.000B'),if(\$1>1000000, num(\$1/1000000,'#,##0.000M'),if(\$1>1000, num(\$1/1000,'#,##0.000K'),num(\$1,'#,##0'))));
SET ServiceAndHardware =sum({<SERVICE_TYPE={'Service','Hardware'}>}Total_Opportunity_Value_USD_Amount);
```

Expression 1: Service

\$(vScaleNumber(sum({<SERVICE_TYPE={'Service'}>}Total_Opportunity_Value_USD_Amount)))

Expression 2: Hardware

\$(vScaleNumber(sum({<SERVICE_TYPE={'Hardware'}>}Total_Opportunity_Value_USD_Amount)))

Expression 3 : Total

if(\$(ServiceAndHardware)>1000000000, num(\$(ServiceAndHardware)/1000000000,'#,##0.000B'),if(\$(ServiceAndHardware)>1000000, num(\$(ServiceAndHardware)/1000000,'#,##0.000M'),if(\$(ServiceAndHardware)>1000, num(\$(ServiceAndHardware)/1000,'#,##0.000K'),num(\$(ServiceAndHardware),'#,##0'))))

Master
Author

thanks arjun...  Not yet tested.    i will test and update you

Community Browser