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: 
soniasweety
Master
Master

wrong sum

Hi All,

tresesco

felipedl

kaushik.solanki

jyothish8807

neelamsaroha1575

dx.anupam

balabhaskarqlik

simone.spanio


i am using   pivot table

Dimensionsumtotal.PNG :

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
qlikviewwizard
Master II
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'))))

Capture.PNG

View solution in original post

10 Replies
MarcoWedel

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

soniasweety
Master
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 typ.PNG

soniasweety
Master
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?

MarcoWedel

please post a sample application to demonstrate

soniasweety
Master
Master
Author

Hi marcowedel  

PFA sample

MarcoWedel

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

soniasweety
Master
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

qlikviewwizard
Master II
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'))))

Capture.PNG

soniasweety
Master
Master
Author

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