Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist III
Specialist III

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

Tags (2)
1 Solution

Accepted Solutions
Highlighted
Master II
Master II

Re: wrong sum

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
Highlighted

Re: wrong sum

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

Highlighted
Specialist III
Specialist III

Re: wrong sum

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

Highlighted
Specialist III
Specialist III

Re: wrong sum

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?

Highlighted

Re: wrong sum

please post a sample application to demonstrate

Highlighted
Specialist III
Specialist III

Re: wrong sum

Hi marcowedel  

PFA sample

Highlighted

Re: wrong sum

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

Highlighted
Specialist III
Specialist III

Re: wrong sum

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

Highlighted
Master II
Master II

Re: wrong sum

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

Highlighted
Specialist III
Specialist III

Re: wrong sum

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